topic Re: Proc transpose with static variable in SAS Programming

Proc transpose with static variable

Konkordanz — Tue, 27 Jul 2021 07:21:45 GMT

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;

Re: Proc transpose with static variable

ballardw — Tue, 27 Jul 2021 07:27:19 GMT

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.

Re: Proc transpose with static variable

Kurt_Bremser — Tue, 27 Jul 2021 07:57:55 GMT

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:

Data is presented as a data step with datalines, which makes it ver easy for everyone reading this to recreate the dataset without second guessing attributes or content. Please do so yourself in the future.
How you determine the value for bev may be different (e.g. from the first observation of have).

Re: Proc transpose with static variable

Konkordanz — Tue, 27 Jul 2021 08:48:50 GMT

Its working! Thank you so much! So far I didnt work with proc sql, but obviously its pretty powerful 😉 Thx!