BookmarkSubscribeRSS Feed
TapioKalmi
SAS Employee

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!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Discussion stats
  • 0 replies
  • 655 views
  • 3 likes
  • 1 in conversation