Creating flexible variables based on row values

[EinarRoed]

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?

[PeterClemmensen · Accepted solution]

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;

[EinarRoed]

Perfection. Thank you. 🙂

[PeterClemmensen]

Anytime 🙂

[EinarRoed]

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.

[PeterClemmensen]

Show me your clunky approach?

[EinarRoed]

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).

[PeterClemmensen]

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;

[EinarRoed]

Most definitely. Thanks again.