Does anyone know how I could achieve this.
I have a SAS data with 495 variable and I observation. I want to transpose the variable and create 495 observation. I don’t know how to do this in do loop.
Here is an example
SAS data set
Var1 var1_value var2 var2_value var3 var3_value var4 var4_value
subj 2 owner 0 prop 1 doc 1
I want this
Variable value
Subj 2
Owner 0
Prop 1
doc 1
Thanks in advance
Do you really need to do it using a do loop? It would call for a lot less typing if you created and used a macro variable. e.g.:
proc sql noprint;
select "variable="||strip(name)||"; value="||strip(name)||"_value;output;"
into :varnames separated by " "
from dictionary.columns
where libname="WORK" and
memname="HAVE" and
name not contains "_value"
;
quit;
data want ;
set have;
&varnames.;
keep variable value ;
run;
Use two arrays.
data want ;
set have ;
array name var1 var2 ...... ;
array number var1_value var2_value .... ;
do i =1 to dim(name);
variable = name(i);
value = number(i);
output;
end;
keep variable value ;
run;
IF all of the character variables have the names and all of the numeric variables have the values AND your variables are defined in a logic order you might be able to avoid listing all of the names.
data want ;
set have ;
array name _character_;
array number _numeric_ ;
...
Do you really need to do it using a do loop? It would call for a lot less typing if you created and used a macro variable. e.g.:
proc sql noprint;
select "variable="||strip(name)||"; value="||strip(name)||"_value;output;"
into :varnames separated by " "
from dictionary.columns
where libname="WORK" and
memname="HAVE" and
name not contains "_value"
;
quit;
data want ;
set have;
&varnames.;
keep variable value ;
run;
Art:
I don't know that it is really a lot less typing to apply the do loop, as long as there is a way to avoid typing the list of varible names. One way is the sql way you suggest.
Another is to use Tom's solution, but instead of listing the variables, use the keywords _numeric_ and _character_ as in;
data want (keep=name value);
set have;
array nam {*} $32 _character_;
array val{*} _numeric_ ;
do I=1 to dim(nam);
name=nam{I};
value=val{I};
output;
end;
run;
For folks unfamiliar with the "select ... into" and dictionary.xxx techniques, the array approach is probably more intuitive.
BUT the downside ... while this technique free of requiring a naming converntion for the variable names (i.e. you don't need varnames of xxx and xxx_value) , it absolutely depends on the order of the "name" vars to correspond with the order of the numeric value variables.
regards,
Mark
Thanks everyone
Just curious, how come your raw data ends up with an odd number of obs(495)? Does it mean your data does not have a complete pairwise match? The following method is based on the assumption that your data strictly follows Variable-Value order and no skipping, it uses DOW, while Mod() will do the same.
proc transpose data=have out=have1;
var _all_;
run;
data want;
do _n_=1 to 2;
set have1;
if _n_=1 then variable=col1;
else value=input(col1,best.);
end;
keep variable value;
run;
Haikuo
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.