topic Row to variable in SAS Programming

Row to variable

rhapsody — Wed, 19 Dec 2018 16:16:55 GMT

I have a table looking like this

Case Variable Y112 Y113 Y114

Basic Temperature 20 23 25

Basic Speed 130 120 80

Serious Temperature 19 21 22

Serious Speed 140 110 90

The table is big with many different variables and Cases. As an example, if I pick the Case Basic I want the variables (Temperature and Speed) to end up like this in a new table

Temperature1 Temperature2 Temperature3 Speed1 Speed2 Speed3

20 23 25 130 120 80

So far I've done this but it only works woth one variable. But imagine that besides Temperature and Speed I have maybe 20 more variables.

%let case1=Basic;

data out_Table;

set orig_Table; where Case="&case1." AND Index='Temperature';

rename Y112-Y114=Temperature1-Temperature5;

run;

Re: Row to variable

Reeza — Wed, 19 Dec 2018 16:47:32 GMT

You can use a double transpose which is fully dynamic. Please post your data as a data step rather than as text.

*Create sample data;
data have;
informat Case Variable $15.;
input Case $  Variable $ Y112 Y113 Y114;
cards;
Basic Temperature 20 23 25
Basic Speed 130 120 80
Serious Temperature 19 21 22
Serious Speed 140 110 90
;;;;
run;

*sort to transpose;
proc sort data=have;
by case variable;

*create long data set;
proc transpose data=have out=long1;
by case variable;
var Y112-Y114;
run;

*add in index to go to wide;
data long2;
set long1;
by case variable;
if first.variable then counter=1;
else counter+1;
run;

*go to wide format;
proc transpose data=long2 out=wide1;
by case;
id variable counter;
idlabel variable;
var col1;
run;

@rhapsody wrote:

I have a table looking like this

Case                                 Variable                            Y112               Y113                    Y114

Basic                                  Temperature                    20                   23                         25

Basic                                  Speed                             130                 120                       80

Serious                              Temperature                    19                    21                        22

Serious                               Speed                              140                 110                       90

The table is big with many different variables and Cases. As an example, if I pick the Case Basic I want the variables (Temperature and Speed) to end up like this in a new table

Temperature1        Temperature2         Temperature3        Speed1           Speed2           Speed3

20                                    23                            25                    130                 120                  80

So far I've done this but it only works woth one variable. But imagine that besides Temperature and Speed I have maybe 20 more variables.

%let case1=Basic;

data out_Table;

set orig_Table; where Case="&case1." AND Index='Temperature';

rename Y112-Y114=Temperature1-Temperature5;

run;

Re: Row to variable

learsaas — Thu, 20 Dec 2018 08:00:14 GMT

%macro trans(case1=,index1=);
	%local n1 n2 index1 case1 VarName;

	%let n1=%sysfunc(countw(&case1,%str(|)));
	%let n2=%sysfunc(countw(&Index1,%str(|)));
	proc sort data=orig_Table out=out_Table;
		by Case Variable;
		where 1=1 
		%do i=1 %to &n1;
			%if &i eq 1 %then %do;
				AND Case in (
				"%qscan(&Case1,&i,%str(|))"
			%end;
			%else %do;
				,"%qscan(&Case1,&i,%str(|))"
			%end;
			%if &i eq &n1 %then %do;
			    )
			%end;
		%end;
		%do i=1 %to &n2;
			%if &i eq 1 %then %do;
				AND Variable in (
				"%qscan(&Index1,&i,%str(|))"
			%end;
			%else %do;
				,"%qscan(&Index1,&i,%str(|))"
			%end;
			%if &i eq &n2 %then %do;
			    )
			%end;
		%end;
		;
	run;
	%if &n2 eq 0 %then %do;
		proc sql noprint;
			select distinct strip(Variable) into :Index1 separated by '|'
			from out_Table 
			where Variable^=''
			;
		quit;
		%let n2=%sysfunc(countw(&Index1,%str(|)));
	%end;
	data out_Table;
		set out_Table;
		by Case Variable;
		array array_Y Y112-Y114;
		
		%do i=1 %to &n2;
			%let VarName=%qscan(&Index1,&i,%str(|));
			 array  %unquote(A_&Varname &Varname.1-&Varname.3);
			 retain %unquote(&VarName.1-&VarName.3);
		%end;
		if first.Case then do;
			%do i=1 %to &n2;
				%let VarName=%qscan(&Index1,&i,%str(|));
				call missing(of %unquote(A_&Varname.{*}));
			%end;		
		end;
		%do i=1 %to &n2;
			%let VarName=%qscan(&Index1,&i,%str(|));
			%if &i eq 1 %then %do;
				select;
			%end;
		   when(Variable="&VarName") do over array_Y;
		   		%unquote(A_&VarName)=array_Y;
		   end;
			%if &i eq &n2 %then %do;
			   otherwise do;
			   end;
			   end;
			%end;
		%end;
		if last.Case;
		drop Variable Y112-Y114;
	run;
%mend;
%trans(case1=Basic,index1=Temperature|Speed);
/*%trans(case1=Basic|Serious,index1=Temperature|Speed);*/
/*%trans(case1=Basic);*/
/*%trans(case1=Basic,index1=Temperature);*/
/*%trans(case1=Basic);*/
/*%trans(index1=Temperature);*/
/*%trans();*/

Re: Row to variable

ShiroAmada — Thu, 20 Dec 2018 08:36:13 GMT

Try this...

data sample;
infile datalines dlm="|";
input 
Case:$30.
vars: $30.
Y112: 8.
Y113:8.
Y114:8.
;
datalines;
Basic|Temperature|20|23|25
Basic|Speed|130|120|80
Serious|Temperature|120|123|125
Serious|Speed|230|220|280
;
run;

proc sort data=sample;
  by case ;
run;

data all;
  merge
  sample(where=(vars='Speed') rename=(Y112=Speed1 Y113=Speed2 Y114=Speed3))
  sample(where=(vars^='Speed') rename=(Y112=Temp1 Y113=Temp2 Y114=Temp3 ));
  by case;
  drop vars;
run;