Hi,
ive the following Data-Set:
Year | bev | V01 | V02 | V03 | Vn |
4063400 | 7399 | 7146 | 4120 | ... | |
2019 | 3889 | 3750 | 2220 | ... | |
2020 | 4063400 | 3510 | 3397 | 1900 | ... |
What I want is: Transpose all variables except of the empty line. The Var Bev should has a static value over all rows.
Year | 2019 | 2020 | bev |
V01 | 3889 | 3510 | 4063400 |
V02 | 3750 | 3397 | 4063400 |
V03 | 2220 | 1900 | 4063400 |
Vn | … | … | … |
Ive tried it with this Code. The Result looks fine, but it doesnt creates the column Bev. Would be great to get some help! Thank you!
proc transpose data=test Name=Kontonummer präfix=Jahr out=test;
id Jahr;
var v01-v89;
run;
You will need an additional step to set bev:
data have;
infile datalines dlm=" " dsd truncover;
input Year bev V01 V02 V03;
datalines;
4063400 7399 7146 4120
2019 3889 3750 2220
2020 4063400 3510 3397 1900
;
proc sql noprint;
select max(bev) into :bev from have;
quit;
proc transpose
data=have
out=trans (rename=(_name_=year))
prefix=Y_
;
var V:;
id year;
run;
data want;
set trans;
bev = &bev;
run;
Note:
Except for the special variable like _name_ that might be added by proc transpose it will not create new values. You have to either populate that Bev value prior to the transpose or after, whichever makes more sense or is easiest.
You will need an additional step to set bev:
data have;
infile datalines dlm=" " dsd truncover;
input Year bev V01 V02 V03;
datalines;
4063400 7399 7146 4120
2019 3889 3750 2220
2020 4063400 3510 3397 1900
;
proc sql noprint;
select max(bev) into :bev from have;
quit;
proc transpose
data=have
out=trans (rename=(_name_=year))
prefix=Y_
;
var V:;
id year;
run;
data want;
set trans;
bev = &bev;
run;
Note:
Its working! Thank you so much! So far I didnt work with proc sql, but obviously its pretty powerful 😉 Thx!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: