Could you please help in transposing the data
Category | Var_1 | Var_2 | Var_3 | Var_4 | Var_5 | Count |
0 | 0 | 100 | ||||
0 | 1 | 200 | ||||
0 | 2 | 300 | ||||
0 | 3 | 400 | ||||
0 | 4 | 500 | ||||
0 | 5 | 600 | ||||
0 | 0 | 700 | ||||
0 | 1 | 800 | ||||
0 | 2 | 900 | ||||
0 | 3 | 1000 | ||||
0 | 4 | 1100 | ||||
0 | 5 | 1200 | ||||
0 | 0 | 1300 | ||||
0 | 1 | 1400 | ||||
0 | 2 | 1500 | ||||
0 | 3 | 1600 | ||||
0 | 4 | 1700 | ||||
0 | 5 | 1800 | ||||
0 | 0 | 1900 | ||||
0 | 1 | 2000 | ||||
0 | 2 | 2100 | ||||
0 | 3 | 2200 | ||||
0 | 4 | 2300 | ||||
0 | 5 | 2400 | ||||
0 | 0 | 2500 | ||||
0 | 1 | 2600 | ||||
0 | 2 | 2700 | ||||
0 | 3 | 2800 | ||||
0 | 4 | 2900 | ||||
0 | 5 | 3000 | ||||
1 | 0 | 3100 | ||||
1 | 1 | 3200 | ||||
1 | 2 | 3300 | ||||
1 | 3 | 3400 | ||||
1 | 4 | 3500 | ||||
1 | 5 | 3600 | ||||
1 | 0 | 3700 | ||||
1 | 1 | 3800 | ||||
1 | 2 | 3900 | ||||
1 | 3 | 4000 | ||||
1 | 4 | 4100 | ||||
1 | 5 | 4200 | ||||
1 | 0 | 4300 | ||||
1 | 1 | 4400 | ||||
1 | 2 | 4500 | ||||
1 | 3 | 4600 | ||||
1 | 4 | 4700 | ||||
1 | 5 | 4800 | ||||
1 | 0 | 4900 | ||||
1 | 1 | 5000 | ||||
1 | 2 | 5100 | ||||
1 | 3 | 5200 | ||||
1 | 4 | 5300 | ||||
1 | 5 | 5400 | ||||
1 | 0 | 5500 | ||||
1 | 1 | 5600 | ||||
1 | 2 | 5700 | ||||
1 | 3 | 5800 | ||||
1 | 4 | 5900 | ||||
1 | 5 | 6000 |
Variables | Category | _0 | _1 |
Var_1 | 0 | 100 | 3100 |
Var_1 | 1 | 200 | 3200 |
Var_1 | 2 | 300 | 3300 |
Var_1 | 3 | 400 | 3400 |
Var_1 | 4 | 500 | 3500 |
Var_1 | 5 | 600 | 3600 |
Var_2 | 0 | 700 | 3700 |
Var_2 | 1 | 800 | 3800 |
Var_2 | 2 | 900 | 3900 |
Var_2 | 3 | 1000 | 4000 |
Var_2 | 4 | 1100 | 4100 |
Var_2 | 5 | 1200 | 4200 |
Var_3 | 0 | 1300 | 4300 |
Var_3 | 1 | 1400 | 4400 |
Var_3 | 2 | 1500 | 4500 |
Var_3 | 3 | 1600 | 4600 |
Var_3 | 4 | 1700 | 4700 |
Var_3 | 5 | 1800 | 4800 |
Var_4 | 0 | 1900 | 4900 |
Var_4 | 1 | 2000 | 5000 |
Var_4 | 2 | 2100 | 5100 |
Var_4 | 3 | 2200 | 5200 |
Var_4 | 4 | 2300 | 5300 |
Var_4 | 5 | 2400 | 5400 |
Var_5 | 0 | 2500 | 5500 |
Var_5 | 1 | 2600 | 5600 |
Var_5 | 2 | 2700 | 5700 |
Var_5 | 3 | 2800 | 5800 |
Var_5 | 4 | 2900 | 5900 |
Var_5 | 5 | 3000 | 6000 |
Please clarify your question. I assume the second part is your have data? If so then your first example is not a transpose, you are just moving some data across. Unfortunately that example data does not match the have data either, ok category 0 = count 100, but where do you get category 0 for the next row being 200 as it is 700 in your example? Post exact data, as a datastep, and an example of the reuquired output for that data. As an example something like this gets near, but with the differences between the two given data doesn't match:
data have; input vars $ num count x; datalines; Var_1 0 100 3100 Var_1 1 200 3200 Var_1 2 300 3300 Var_1 3 400 3400 Var_1 4 500 3500 Var_1 5 600 3600 Var_2 0 700 3700 Var_2 1 800 3800 Var_2 2 900 3900 Var_2 3 1000 4000 Var_2 4 1100 4100 Var_2 5 1200 4200 ; run; data want (drop=vars num); set have; array var_{5} 8.; select(vars); when ('Var_1') var_{1}=num; when ('Var_2') var_{2}=num; when ('Var_3') var_{3}=num; when ('Var_4') var_{4}=num; when ('Var_5') var_{5}=num; otherwise; end; run;
Hi
The sample code below transposes your data.
The first DATA Step creates the data you have
The second DATA Step adds the variables needed for Proc TRANSPOSE
options
validvarname=v7
;
/* create orginal data */
data have;
infile cards;
input
@1 cat 2.
@5 var_1 2.
@9 var_2 2.
@13 var_3 2.
@17 var_4 2.
@21 var_5 2.
@25 count 8.
;
cards;
0 0 100
0 1 200
0 2 300
0 3 400
0 4 500
0 5 600
0 0 700
0 1 800
0 2 900
0 3 1000
0 4 1100
0 5 1200
0 0 1300
0 1 1400
0 2 1500
0 3 1600
0 4 1700
0 5 1800
0 0 1900
0 1 2000
0 2 2100
0 3 2200
0 4 2300
0 5 2400
0 0 2500
0 1 2600
0 2 2700
0 3 2800
0 4 2900
0 5 3000
1 0 3100
1 1 3200
1 2 3300
1 3 3400
1 4 3500
1 5 3600
1 0 3700
1 1 3800
1 2 3900
1 3 4000
1 4 4100
1 5 4200
1 0 4300
1 1 4400
1 2 4500
1 3 4600
1 4 4700
1 5 4800
1 0 4900
1 1 5000
1 2 5100
1 3 5200
1 4 5300
1 5 5400
1 0 5500
1 1 5600
1 2 5700
1 3 5800
1 4 5900
1 5 6000
;
data have2;
set have;
array xvar{*} var_1 - var_5;
/* fill the variables and category with the appropriate values */
do i = 1 to dim(xvar);
if missing(xvar{i}) = 0 then do;
length variables $ 32;
variables = vname(xvar{i});
category = xvar{i};
end;
end;
run;
/* sort the data s needed for transpose */
proc sort data=have2;
by variables category;
run;
/* transpose the data */
proc transpose
data=have2
out=want(drop=_name_)
;
by variables category;
var count;
id cat;
run;
Bruno
Check MERGE skill for such kind of question :
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
infile cards;
input
@1 cat 2.
@5 var_1 2.
@9 var_2 2.
@13 var_3 2.
@17 var_4 2.
@21 var_5 2.
@25 count 8.
;
cards;
0 0 100
0 1 200
0 2 300
0 3 400
0 4 500
0 5 600
0 0 700
0 1 800
0 2 900
0 3 1000
0 4 1100
0 5 1200
0 0 1300
0 1 1400
0 2 1500
0 3 1600
0 4 1700
0 5 1800
0 0 1900
0 1 2000
0 2 2100
0 3 2200
0 4 2300
0 5 2400
0 0 2500
0 1 2600
0 2 2700
0 3 2800
0 4 2900
0 5 3000
1 0 3100
1 1 3200
1 2 3300
1 3 3400
1 4 3500
1 5 3600
1 0 3700
1 1 3800
1 2 3900
1 3 4000
1 4 4100
1 5 4200
1 0 4300
1 1 4400
1 2 4500
1 3 4600
1 4 4700
1 5 4800
1 0 4900
1 1 5000
1 2 5100
1 3 5200
1 4 5300
1 5 5400
1 0 5500
1 1 5600
1 2 5700
1 3 5800
1 4 5900
1 5 6000
;
run;
data temp;
set have;
by cat ;
length Variables $ 40;
if first.cat then n=0;
Category=coalesce(of var_1-var_5);
if Category=0 then n+1;
Variables=catx('_','var',n);
keep cat Category Variables count ;
run;
proc sql;
select distinct cats('temp(where=(cat=',cat,') rename=(count=_',cat,'))') into : merge separated by ' '
from temp;
quit;
data want;
merge &merge ;
by Variables Category;
drop cat;
run;
Could you please tell me how cats function will work in following proc sql?
proc sql;
select distinct cats('temp(where=(cat=',cat,') rename=(count=_',cat,'))') into : merge separated by ' '
from temp;
quit;
You could take this SQL as same as
proc sql;
select distinct cat
from temp;
quit;
It just add some other words into it . You will see what code it would generate.
select distinct cats('temp(where=(cat=',cat,') rename=(count=_',cat,'))') into : merge separated by ' '
Concatenate
"temp(where=(cat="
the value of the variable CAT
") rename=(count=_"
the value of the variable CAT
"))"
And put the resuting string into the macro variable MERGE with a space between each distinct string.
data have;
infile cards;
input
@1 cat 2.
@5 var_1 2.
@9 var_2 2.
@13 var_3 2.
@17 var_4 2.
@21 var_5 2.
@25 count 8.
;
cards;
0 0 100
0 1 200
0 2 300
0 3 400
0 4 500
0 5 600
0 0 700
0 1 800
0 2 900
0 3 1000
0 4 1100
0 5 1200
0 0 1300
0 1 1400
0 2 1500
0 3 1600
0 4 1700
0 5 1800
0 0 1900
0 1 2000
0 2 2100
0 3 2200
0 4 2300
0 5 2400
0 0 2500
0 1 2600
0 2 2700
0 3 2800
0 4 2900
0 5 3000
1 0 3100
1 1 3200
1 2 3300
1 3 3400
1 4 3500
1 5 3600
1 0 3700
1 1 3800
1 2 3900
1 3 4000
1 4 4100
1 5 4200
1 0 4300
1 1 4400
1 2 4500
1 3 4600
1 4 4700
1 5 4800
1 0 4900
1 1 5000
1 2 5100
1 3 5200
1 4 5300
1 5 5400
1 0 5500
1 1 5600
1 2 5700
1 3 5800
1 4 5900
1 5 6000
;
run;
* transpose 1 ;
proc transpose data=have out=want1;
var var_1-var_5;
by count cat;
run;
* transpose 2 ;
proc sort data=want1;
by _name_ col1;
run;
proc transpose data=want1 out=want2;
var count;
by _name_ col1;
id cat;
where col1^=.;
run;
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.