topic Re: Data transpose in SAS Programming

Data transpose

SMohanReddy — Mon, 04 Jan 2016 12:09:17 GMT

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

Re: Data transpose

RW9 — Mon, 04 Jan 2016 13:18:03 GMT

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;

Re: Data transpose

BrunoMueller — Mon, 04 Jan 2016 13:39:58 GMT

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

Re: Data transpose

Ksharp — Tue, 05 Jan 2016 06:26:25 GMT

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;

Re: Data transpose

Babloo — Tue, 05 Jan 2016 09:27:56 GMT

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;

Re: Data transpose

Ksharp — Tue, 05 Jan 2016 09:56:39 GMT

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.

Re: Data transpose

RW9 — Tue, 05 Jan 2016 10:04:47 GMT

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.

Re: Data transpose

amats — Wed, 06 Jan 2016 03:56:26 GMT

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;