topic Re: Creating flexible variables based on row values in New SAS User

Creating flexible variables based on row values

EinarRoed — Mon, 12 Aug 2019 07:28:12 GMT

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?

Re: Creating flexible variables based on row values

PeterClemmensen — Mon, 12 Aug 2019 07:34:56 GMT

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;

Re: Creating flexible variables based on row values

EinarRoed — Mon, 12 Aug 2019 07:51:08 GMT

Perfection. Thank you. 🙂

Re: Creating flexible variables based on row values

PeterClemmensen — Mon, 12 Aug 2019 08:00:04 GMT

Anytime 🙂

Re: Creating flexible variables based on row values

EinarRoed — Mon, 12 Aug 2019 09:01:23 GMT

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.

Re: Creating flexible variables based on row values

PeterClemmensen — Mon, 12 Aug 2019 09:02:09 GMT

Show me your clunky approach?

Re: Creating flexible variables based on row values

EinarRoed — Mon, 12 Aug 2019 09:19:04 GMT

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

Re: Creating flexible variables based on row values

PeterClemmensen — Mon, 12 Aug 2019 09:28:18 GMT

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;

Re: Creating flexible variables based on row values

EinarRoed — Mon, 12 Aug 2019 09:40:05 GMT

Most definitely. Thanks again.