topic Rearranging data in SAS 9.4 in SAS Data Management

Rearranging data in SAS 9.4

Thomas_mp — Sun, 05 Jul 2020 18:47:01 GMT

Good morning,

I need some help rearranging data in a very large data set using SAS 9.4.

I have the data as follows (please see also attached Excel file) :

data k;

input date va vb vc vd ;

cards ;

20000630 6.1207 8.7044 5.3283 5.846

20000929 6.1158 8.6934 5.3253 5.8423

20001229 5.7898 7.9828 5.1254 5.5832

20010330 4.9815 7.5266 4.4934 4.5082

;

run;

I would like to have the data as:

va	20000630	6.1207
va	20000929	6.1158
va	20001229	5.7898
va	20010330	4.9815
vb	20000630	8.7044
vb	20000929	8.6934
vb	20001229	7.9828
vb	20010330	7.5266
vc	20000630	5.3283
vc	20000929	5.3253
vc	20001229	5.1254
vc	20010330	4.4934
vd	20000630	5.846
vd	20000929	5.8423
vd	20001229	5.5832
vd	20010330	4.5082

Thank you for your help,

Tomas

Re: Rearranging data in SAS 9.4

TomKari — Sun, 05 Jul 2020 21:24:07 GMT

Thanks for posting the data step to make it easy!

proc transpose data=k out=ktrans;
	var va--vd;
	by date;
run;

proc sort;
	by _name_ date;
run;

Re: Rearranging data in SAS 9.4

Ksharp — Mon, 06 Jul 2020 12:29:08 GMT

proc sql;
create table want as
select 'va' as label length=32,date,va as value from k
union all
select 'vb' as label length=32,date,vb as value from k
union all
.....................
quit;

Re: Rearranging data in SAS 9.4

Thomas_mp — Mon, 06 Jul 2020 15:17:57 GMT

Thank you Tom,

Regards,

Tomas

Re: Rearranging data in SAS 9.4

Thomas_mp — Mon, 06 Jul 2020 15:22:10 GMT

Thank you Diamond,

The Proc transpose works in this case.

One obstacle to implement your code is that I have 5000 variables Va, Vb,.... (etc),

But I appreciate you have taken time to help me,

Regards,

Tomas