topic Re: Converting from wide to long and creating new variable in SAS Programming

Converting from wide to long and creating new variable

corkee — Mon, 14 Aug 2017 17:51:41 GMT

Hi, I reshaped my dataset from long to wide and now need to change it back to long. The code I used to convert from long to wide is:

proc sort data=medicaid_new;
	by ParticipantID;
run;

proc transpose data=medicaid_new out=medicaid_wide1 prefix=HEDIS;
	by ParticipantID;
	id Time;
	var HEDIS;
run;

The time variable has values ranging from -15 to 15. Is it possible to reshape it from wide to long and to create a new "Time" variable with its original values? In wide format, the HEDIS variables are called: HEDIS_15 for Time = -15, HEDIS_14 for Time = -14, ... HEDIS15 for Time = 15. Any help would be greatly appreciated.

Re: Converting from wide to long and creating new variable

Reeza — Mon, 14 Aug 2017 17:54:29 GMT

No, SAS variables need to start with a letter or _.

You can assign the label though, using the IDLABEL statement.

Re: Converting from wide to long and creating new variable

corkee — Mon, 14 Aug 2017 18:01:36 GMT

Hi Reeza, maybe my post isn't clear. Essentially, I want the dataset to look like this:
ID HEDIS Time
1 40 -15
1 35 -14
1 35 -13
...
1 36 14
1 37 15

Where the Time variable is based on the HEDIS_15 and etc. variables in the wide dataset (HEDIS_15 is Time -15 and HEDIS15 is Time 15).

Re: Converting from wide to long and creating new variable

Reeza — Mon, 14 Aug 2017 19:14:38 GMT

Yeah, sorry, didn't catch that.

Use COMPRESS() within a second data step to remove the letters then and keep the numbers and - symbol.

Re: Converting from wide to long and creating new variable

PGStats — Tue, 15 Aug 2017 03:20:54 GMT

Simpler to use a data step in ths case:

data medicaid_long;
set medicaid_wide;
array h hedis_15 -- hedis15;
do time = -15 to 15;
	HEDIS = h{time};
	output;
	end;
keep participantID time HEDIS;
run;