topic Re: Transpose data and date format in SAS Programming

Transpose data and date format

Q1983 — Mon, 15 Oct 2018 01:26:20 GMT

data HAVE;

input Group $ _NAME_ $ mth_28FEB2013 mth_31MAR2013;

datalines;

Central AAPP 1000 2500

Pacific BBER 1 100

PACIFIC BRMM 2 150

;

run;

Group	_NAME_	mth_28FEB2013	mth_31MAR2013
Central	AAPP	1000	2500
Pacific	BBER	1	100
PACIFIC	BRMM	2	150

I want to transpose based on the month entry(current a character. I would also need to define a new variable called value to "house" the number from the date I need to read it as a date9 as follows

Group	_NAME_	Month	Value
Central	AAPP	28FEB2013	1000
Central	AAPP	31MAR2013	2500
Pacific	BBER	28FEB2013	1
Pacific	BBER	31MAR2013	100
Pacific	BRMM	28FEB2013	2
Pacific	BRMM	31MAR2013	150

Re: Transpose data and date format

Reeza — Mon, 15 Oct 2018 01:36:18 GMT

Have you attempted a PROC TRANSPOSE. This is exactly what it's designed for and will handle this automatically.

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

Re: Transpose data and date format

Astounding — Mon, 15 Oct 2018 02:11:19 GMT

Are you able to go back to your raw data? The DATA step can handle this based on the original data:

data want;

input Group $ _NAME_ $ @;

Month = '28FEB2013'd;

input value @;

output;

month = '31MAR2013'd;

input value;

output;

format month date9.;

run;

Re: Transpose data and date format

novinosrin — Mon, 15 Oct 2018 04:04:35 GMT


data HAVE;

input Group $ _NAME_ $ mth_28FEB2013 mth_31MAR2013;

datalines;
Central AAPP 1000 2500
Pacific BBER 1 100
PACIFIC BRMM 2 150
;
run;

proc transpose data=have(rename=(_name_=name)) out=want;
by group name  notsorted;
var mth:;
run;

data want;
set have;
array j mth:;
do over j;
month=scan(vname(j),-1, '_');
value=j;
output;
end;
drop mth:;
run;

Re: Transpose data and date format

singhsahab — Mon, 15 Oct 2018 02:56:55 GMT

Hi Q,

Before transpose you have to change the Original variable name (_name_) to "name" or any name as per your requirement. Here you can go with a simple solution.

data HAVE;
	input Group $ NAME $ mth_28FEB2013 mth_31MAR2013;
	datalines;

 

Central AAPP 1000 2500

Pacific BBER 1 100

PACIFIC BRMM 2 150

;
run;

proc sort data=have;
	by Group name;
run;

proc transpose data=have out=have1 (rename=(_name_=month col1=value));
	by group name;
	var mth_:;
run;

data want (drop=month rename=(month1=month));
	set have1;
	month1=input(substr(month, find(month, '_', 1)+1), date9.);
	format month1 date9.;
run;