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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.