DATA Step, Macro, Functions and more

Data transpose

Reply
Occasional Contributor
Posts: 18

Data transpose

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
Super User
Super User
Posts: 7,401

Re: Data transpose

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;
SAS Super FREQ
Posts: 683

Re: Data transpose

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

Super User
Posts: 9,681

Re: Data transpose

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;
Super Contributor
Posts: 426

Re: Data transpose

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;

 

 

Super User
Posts: 9,681

Re: Data transpose

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.

Super User
Super User
Posts: 7,401

Re: Data transpose

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.

Occasional Contributor
Posts: 8

Re: Data transpose

[ Edited ]
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;
Ask a Question
Discussion stats
  • 7 replies
  • 298 views
  • 1 like
  • 6 in conversation