topic Re: Proc SQL: Combine Multiple Row Values into One in SAS Programming

Proc SQL: Combine Multiple Row Values into One

Agent1592 — Tue, 22 Nov 2016 07:10:51 GMT

I have the following data: How can I collapse these two rows into 1 using the SQL procedure:

ID	Type	Rate	Amt	Trend	event	final	carryover	comment
1	A	2						Text1
1	B	0.7		decreasing	0.625			Text2

Re: Proc SQL: Combine Multiple Row Values into One

LinusH — Tue, 22 Nov 2016 07:14:57 GMT

Why limit yourself to SQL?
Also how do you want to collapse since there are different values in the same column?
Pls provide a want data set.

Re: Proc SQL: Combine Multiple Row Values into One

Agent1592 — Tue, 22 Nov 2016 07:29:30 GMT

I do not necessarily need SQL. I just want to count the number By Type: This is my want table.

ID	Count Number of Type
1	2

Re: Proc SQL: Combine Multiple Row Values into One

PeterClemmensen — Tue, 22 Nov 2016 07:31:12 GMT

I agree with @LinusH, but if you absolutely have to do this in sql you have to make the decision of what value you want in eg Type and Rate? 🙂

Re: Proc SQL: Combine Multiple Row Values into One

PeterClemmensen — Tue, 22 Nov 2016 07:44:28 GMT

Like this?

data have;
   input ID Type $ Rate Amt $ Trend :$20. event	final	carryover comment $;
   infile datalines dlm = ',';
   datalines;
1,A,2  , ,          , , ,     ,Text1
1,B,0.7, ,decreasing, , ,0.625,Text2
;

proc sql;
   create table want as
   select ID
         ,count(ID) as Count label = 'Count Number of Type'
   from have
   group by ID;
quit;