BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PetePatel
Quartz | Level 8

Hi,

 

I have a dataset that needs to be modified to show trends over time.

 

I think I need to transpose but I'm unsure on the best way to code it.

 

Have:

IDDateDate1FBB
130/09/201830/06/20192.342.47
130/09/201831/03/20192.342.48
130/09/201831/12/20182.342.49
130/09/201830/09/20182.342.50
231/03/201930/06/20192.822.74
231/03/201931/03/20192.822.75
330/06/201831/03/20193.142.69
330/06/201830/06/20183.142.70
330/06/201830/06/20193.142.71
330/06/201831/12/20183.142.72
330/06/201830/09/20183.142.73

 

Need:

IDq0q1q2q3q4q5
12.342.482.492.5  
22.822.75    
33.142.72.712.722.73 

 

q0 always takes the first row of the FB field

q1-qx always takes the second row of the B field onwards (transposed)

 

Can you please suggest ways to code this?

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
data pretrans;
set have;
by id;
if first.id
then do;
  b = fb;
  count = 1;
end;
else count + 1;
_name_ = cats("q",count);
keep id _name_ b;
run;

proc transpose
  data=pretrans
  out=want (drop=_name_)
;
by id;
var b;
id _name_;
run;

For tested code, please supply data in usable form (data step with datalines);

View solution in original post

7 REPLIES 7
vellad
Obsidian | Level 7

You can use:

proc transpose data=have out=want;
    var fb b;
    by id;
run;

to create below dataset.

vellad_0-1602576017661.png

Next you can split this into two datasets FB (keep=ID COL1) and B (keep=ID COL2 COL3 COL4 COL5), based on _NAME_, and merge them and rename variables to Q0, Q1 etc.

 

Kurt_Bremser
Super User
data pretrans;
set have;
by id;
if first.id
then do;
  b = fb;
  count = 1;
end;
else count + 1;
_name_ = cats("q",count);
keep id _name_ b;
run;

proc transpose
  data=pretrans
  out=want (drop=_name_)
;
by id;
var b;
id _name_;
run;

For tested code, please supply data in usable form (data step with datalines);

vellad
Obsidian | Level 7
This would be neat. Minor points: need to retain count and initialize count=0 and not 1 inside the if block.
vellad
Obsidian | Level 7
ah, yes.
jimbarbour
Meteorite | Level 14

That's a bit quirky to have Q1 come from a different variable than Q2 - Q5, but here you go, results, with code below the results.

 

Jim

 

jimbarbour_0-1602579012557.png

 

Code:

DATA	Have;
	INFILE	DATALINES	DSD	DLM='09'x;
	INPUT	ID	
			_Date	:	ANYDTDTE10.
			_Date1	:	ANYDTDTE10.
			_FB	
			_B
			;
	FORMAT	_Date	DDMMYY10.;
	FORMAT	_Date1	DDMMYY10.;
DATALINES;
1	30/09/2018	30/06/2019	2.34	2.47
1	30/09/2018	31/03/2019	2.34	2.48
1	30/09/2018	31/12/2018	2.34	2.49
1	30/09/2018	30/09/2018	2.34	2.50
2	31/03/2019	30/06/2019	2.82	2.74
2	31/03/2019	31/03/2019	2.82	2.75
3	30/06/2018	31/03/2019	3.14	2.69
3	30/06/2018	30/06/2018	3.14	2.70
3	30/06/2018	30/06/2019	3.14	2.71
3	30/06/2018	31/12/2018	3.14	2.72
3	30/06/2018	30/09/2018	3.14	2.73
;
Run;

PROC	SQL	NOPRINT;
	SELECT	LEFT(PUT(MAX(ID_Count), 8.))	
		INTO	:	Max
		FROM	(
			SELECT	COUNT(1)	AS	ID_Count
				FROM	Have
					GROUP	BY	ID
					);
RUN;

%PUT	NOTE:  &=Max;

DATA	Need;
	DROP	_:;

	SET	Have;
		BY	ID;
	RETAIN	_i;
	RETAIN	Q1 - Q&Max;
	ARRAY	QQ	[*]	Q1 - Q&Max;

	IF	FIRST.ID		THEN
		DO;
			Q1			=	_FB;
			_i			=	1;
		END;
	ELSE
		DO;
			_i			+	1;
			QQ[_i]		=	_B;
		END;

	IF	LAST.ID			THEN
		DO;
			OUTPUT;
			CALL	MISSING(of	QQ[*]);
		END;
RUN;

 

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 792 views
  • 4 likes
  • 4 in conversation