I have a dataset with only 1 variable. The number of rows can be different day by day.
How it might look today:
FILE_NM |
POLICY.csv |
CUSTOMER.csv |
PRODUCT.csv |
How it might have looked yesterday:
FILE_NM |
POLICY.csv |
CUSTOMER.csv |
PRODUCT.csv |
COMMISSION.csv |
I want to identify how many rows there were, and then create 1 variable per row.
Yesterday the variables should have been:
file_nm1 = 'POLICY.csv'
file_nm2 = 'CUSTOMER.csv'
file_nm3 = 'PRODUCT.csv'
file_nm4 = 'COMMISSION.csv'
Flexibility is important. If there's more input rows, there should be more variables.
Any suggestions on how I can accomplish this?
Like this?
data have;
input FILE_NM $20.;
datalines;
POLICY.csv
CUSTOMER.csv
PRODUCT.csv
;
data temp;
set have;
x=cats('file_nm', _N_);
run;
proc transpose data=temp out=want(drop=_NAME_);
id x;
var FILE_NM;
run;
Like this?
data have;
input FILE_NM $20.;
datalines;
POLICY.csv
CUSTOMER.csv
PRODUCT.csv
;
data temp;
set have;
x=cats('file_nm', _N_);
run;
proc transpose data=temp out=want(drop=_NAME_);
id x;
var FILE_NM;
run;
Anytime 🙂
Is there also a flexible way to load each variable into a macro variable, such as by working 'call symput' into your code?
&file_nm1
&file_nm2
etc...
I only managed to do it in a very manual/clunky way.
Show me your clunky approach?
Opened the 'want' dataset and used VARNUM() to check if 'filename1' existed (by checking if the returned position was >0). If yes then call symput for filname1. Then the same manually for filename 2-15 (there likely won't ever be more than 15 rows/variables).
Probably easier to do while creating the TEMP data set like this
data have;
input FILE_NM $20.;
datalines;
POLICY.csv
CUSTOMER.csv
PRODUCT.csv
;
data temp;
set have;
call symput(cats('file_nm', _N_), FILE_NM);
x=cats('file_nm', _N_);
run;
%put &file_nm1.;
%put &file_nm2.;
%put &file_nm3.;
proc transpose data=temp out=want(drop=_NAME_);
id x;
var FILE_NM;
run;
Most definitely. Thanks again.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.