Juletip #15 - Keep It Simple Santa (aka KISS)

TapioKalmi — Wed, 15 Dec 2021 09:12:18 GMT

KISS is a wonderful thing, anyday and especially at Holiday Season, anywhere and especially in coding.

Have you ever wanted a simple SAS solution to read in any separated value data into SAS table as Excel does for Excel worksheet? As simple as possible? Well here you will have it in a short macro code.

macro delimited2table(input=,delimiter=%str(;),max_string_len=100,output=);
%if "%kupcase(&delimiter.)" EQ "'09'X" %then %do;
%end;
%else %do;
%let delimiter="&delimiter.";
%end;
data &output.(compress=yes);
length A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ
$ &max_string_len.
;
infile "&input." delimiter=&delimiter. MISSOVER DSD lrecl=32767;
input A $ B $ C $ D $ E $ F $ G $ H $ I $ J $ K $ L $ M $ N $ O $ P $ Q $ R $ S $ T $ U $ V $ W $ X $ Y $ Z $
AA $ AB $ AC $ AD $ AE $ AF $ AG $ AH $ AI $ AJ $ AK $ AL $ AM $ AN $ AO $ AP $ AQ $ AR $ AS $ AT $ AU $ AV $ AW $ AX $ AY $ AZ $
;
run;
%mend;

And here are some sample macro calls to call this macro:

*%delimited2table(input=<file pathname>,delimiter=<delimiter character>,output=<output libref>.<output table name>);
%delimited2table(input=c:\public\comma_delimited.txt,delimiter=%str(,),output=sasuser.comma_delimited_in_sas_table);
%delimited2table(input=c:\public\comma_delimited2.txt,delimiter=%str(;),output=sasuser.comma_delimited2_in_sas_table);
%delimited2table(input=c:\public\comma_delimited3.txt,delimiter=%str(;),output=sasuser.comma_delimited3_in_sas_table);
%delimited2table(input=c:\public\comma_delimited4.txt,delimiter=%str(,),output=sasuser.comma_delimited4_in_sas_table);
%delimited2table(input=c:\public\comma_delimited5.txt,delimiter=%str(,),output=sasuser.comma_delimited5_in_sas_table);
%delimited2table(input=c:\public\pipe_delimited.txt,delimiter=|,output=sasuser.pipe_delimited_in_sas_table);
%delimited2table(input=c:\public\tab_delimited.txt,delimiter='09'x,output=sasuser.tab_delimited_in_sas_table);

If your data has some cells with values longer than 100 characters, just add max_string_len= parameter and a value you need. Maximun value for this parameter is 32767 😉

So here you are, have fun.

By the way, I saw mummy KISSing Santa Claus ... Happy Holidays to everyone!

topic Juletip #15 - Keep It Simple Santa (aka KISS) in SAS Community Nordic

Juletip #15 - Keep It Simple Santa (aka KISS)