topic Re: Using the value of a variable to make and name new variables in New SAS User

Using the value of a variable to make and name new variables

solfay243 — Fri, 11 Oct 2019 15:29:16 GMT

Hi all,

I've got a dataset with a bunch of values in a column, but I'd like to make variable in a new dataset named after those values.

For example what I have is:

data have;
input $ names;
datalines;
A
B
C
D
;
run;

And what I'd like for my end result is:

data want;
input $ A $ B $ C $ D;
datalines;
;
run;

I suppose I could use an array to grab all the values and make them variables with a do loop, but I wanted to see if anyone else had other thoughts.

Re: Using the value of a variable to make and name new variables

novinosrin — Fri, 11 Oct 2019 15:29:27 GMT

did you try PROC TRANSPOSE?

Re: Using the value of a variable to make and name new variables

solfay243 — Fri, 11 Oct 2019 15:30:30 GMT

This isn't quite the same as I'm not looking to shift the form of the data from long to wide, but I'm looking to use the data's values to make new variables which are named after those values.

Re: Using the value of a variable to make and name new variables

novinosrin — Fri, 11 Oct 2019 15:39:04 GMT

I am not sure If i am understanding your requirement. However, looking at your HAVE and WANT seems like a Transpose like

data have;
input  names $;
datalines;
A
B
C
D
;
run;

proc transpose data=have out=w(drop=_name_);
id names;
run;

NOTE: No variables to transpose.
NOTE: There were 4 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.W has 0 observations and 4 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

Re: Using the value of a variable to make and name new variables

Reeza — Fri, 11 Oct 2019 15:41:17 GMT

Can you explain the use case or logic behind this?
SAS variables are not created as easily on the fly so you're likely diving into macros or arrays here at minimum.

EDIT:

Basically, it's possible to do this but it's cumbersome and tedious and there are usually many alternative ways to get a similar functionality that's easier, if you explain your use case/logic.

https://en.wikipedia.org/wiki/XY_problem

Re: Using the value of a variable to make and name new variables

solfay243 — Fri, 11 Oct 2019 15:41:31 GMT

Ah! I was not familiar with the id statement used in proc transpose like this. This would be a lot simpler. Thank you!

Re: Using the value of a variable to make and name new variables

Kurt_Bremser — Fri, 11 Oct 2019 15:42:48 GMT

First, test your steps before posting here. Yours cause ERRORs.

Second, transpose is the way to go:

data have;
input names $;
datalines;
A
B
C
D
;
run;

proc transpose data=have out=trans (drop=_name_);
var names;
id names;
run;

data want;
set trans;
stop;
run;

Re: Using the value of a variable to make and name new variables

mkeintz — Fri, 11 Oct 2019 15:44:23 GMT

You apparently want to read in raw data, with the desired variables for each record arranged in a column.

If the variables are arranged the same way for every observation (i.e. 5 lines of data for each obs), then use "line pointer controls" such as

/ which tells SAS to advance one data line
#n which tells SAS to advance to the n'th line in a group

So, crudely speaking, you could

data want;
   input x1   /   x2 x3   / x4;
run;

which tells SAS to expect 3 lines for each observations, with variable X1 on the first line, X2 and X3 on the 2nd line, and X4 on the third line.

If this is not a correct interpretation of your problem, please show actual data as you have it, and actual data layout as you want it.

Re: Using the value of a variable to make and name new variables

Kurt_Bremser — Fri, 11 Oct 2019 15:45:11 GMT

Heh. I would not have expected that transpose tolerates 'no var statement' without at least a WARNING.

Re: Using the value of a variable to make and name new variables

solfay243 — Fri, 11 Oct 2019 15:46:39 GMT

I am going into making a large macro. The end product is an input control file for use in a macro itself to create a baseline dataset to describe a cohort.

This is just a step in the process of that for me. I'm first creating a dataset of the files from a directory (multiple directories eventually) and then creating flag indicators of the file names themselves and eventually doing some analysis throughout all of those datasets. It's a long procedure so I tried to keep this question simple and was just looking at how others may strategize this step apart from what I was already aware of.

Re: Using the value of a variable to make and name new variables

Reeza — Fri, 11 Oct 2019 15:49:54 GMT

SAS BY group and CLASS statements usually mean you don't need to dummy code your own variables. And even if you do need to create dummy codes, glmselect can help automate that.

Re: Using the value of a variable to make and name new variables

novinosrin — Fri, 11 Oct 2019 16:06:31 GMT

Sir @Kurt_Bremser I learned these plus (obs=0) in transposing variable names from the notes I have taken from excerpts of @PGStats (as we know him as PRODIGYGENIUSstats) and @Ksharp a while ago.

As a matter of fact, PG once showed how to use how to copy just metadata and create a new table using LIKE operator in a peculiar way

Example:

/*Like operator to copy metadata*/
proc sql;
create table want
like sashelp.class;
quit;

proc contents data=want;
run;

Re: Using the value of a variable to make and name new variables

data_null__ — Fri, 11 Oct 2019 16:24:45 GMT

@Kurt_Bremser To output 0 obs you can use where=false;

199  proc transpose data=have out=trans(where=(0) drop=_name_);
200  var names;
201  id names;
202  run;

NOTE: There were 4 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.TRANS has 0 observations and 4 variables.

Re: Using the value of a variable to make and name new variables

Tom — Fri, 11 Oct 2019 17:00:53 GMT

That is not the LIKE operator. It is a different syntax that just happened to use the same name as the LIKE operator.

Re: Using the value of a variable to make and name new variables

Kurt_Bremser — Fri, 11 Oct 2019 17:04:46 GMT

Per SAS vocabulary, it is a SQL procedure component.

Re: Using the value of a variable to make and name new variables

novinosrin — Fri, 11 Oct 2019 17:12:59 GMT

Ah ok. Thank you.