One of my favorite new functions is the cats() function available in SAS v9. It is a compress() like function in that it removes leading and
trailing blanks, but it also concatenates the results. CATS() stands for concatenate and strip. Basically the cats() function takes this
type of assignment statement:
key = trim(left(firstName)) || trim(left(lastName)) ||
trim(left(phone)) || trim(left(zip)) ;
and changes it to this:
key = cats(firstName, lastName, phone, zip);
Generally, the cats() function will return a value with a length of 32767 in the data step. So as always, it's a good idea to use
a length statement on the variable you are assigning to. In this example I might use something like:
length key $200;
The cats() function belongs to a family of cat() functions each doing it's own version of concatenate: cat(), cats(), catt(), catx().
Coming up in version 10, the dog() family of functions! :)
Good function! I haven't kept up with all of the new functions so it is nice to know about some of the better ones.
ReplyDeleteKind of almost unrelated question... is there any difference between using trim(left()) and strip()?
ReplyDeleteThis saved my tail tonight. I actually used the older TRIM format because I needed to concatenate last name and first name from an Excel upload while trimming and putting a comma between the fields. Worked like a charm The final statement was:
ReplyDeleteNAME=TRIM(LEFT(LNAME))||','||TRIM(LEFT(FNAME));
Thanks a bunch.
I am learning how to write macros. I have the following code but it does not do what I think it should do:
ReplyDelete=====================
%macro loop;
FileN Physician FilePath DataSetName;
data abardai1.test;
set abardai1.FilesToImport;
FileN = FileName;
position1=index(FileN,'VPL_')+4;
position2=index(FileN,'.')-7;
physician=substr(FileN,position1,(position2-position1));
FilePath = "M:\MAN\PDI Data\NEW PDI Folders\VPL Processing\B- Validation\Stage 1\Passed\";
DataSetName = "Abardai1."||trim(Physician)||"_Import";
put position1 position2 physician DataSetName;
run;
%do i = 1 %to 1;
proc import out=DataSetName
datafile = ""||FilePath||FILEN||"";
dbms=excel REPLACE;
run;
%end;
%mend;
%loop;
========================
What I want to do: "Build a file path and name by reading the name in another dataset. Then IMPORT that file."
I checked the intermediate varibles and they seem to be compiling the correct file name. But the IMPORT gives me the following error:
===============
FilePath||FILEN;
--
22
76
5 ! dbms=excel REPLACE; run;
ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG,
FILE, OUT, REPLACE, TABLE, _DEBUG_.
ERROR 76-322: Syntax error, statement will be ignored.
===============
I am totally at loss to try and figure out WHY SAS complains with "||", or so it seems. Examples I have seen here seem to suggest that 'a'||'b' should give me ab.
If anyone can help me, I would be very very grateful!
Amin
PS: The file name is like L123_ABC_1234.xls
Be careful with cats function, especially if you try to convert numeric variable: if you provide missing as argument you get VERY unpredictable results.
ReplyDeleteThank You and that i have a tremendous offer you: How Much Home Renovation Cost house renovation budget template
ReplyDelete