DATA Step, Macro, Functions and more

Proc SQL: Combine Multiple Row Values into One

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Proc SQL: Combine Multiple Row Values into One

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

 

IDTypeRateAmtTrendeventfinalcarryovercomment
1A2     Text1
1B0.7 decreasing0.625  Text2

Accepted Solutions
Solution
‎11-22-2016 02:00 PM
PROC Star
Posts: 553

Re: Proc SQL: Combine Multiple Row Values into One

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;

View solution in original post


All Replies
Super User
Posts: 5,260

Re: Proc SQL: Combine Multiple Row Values into One

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.
Data never sleeps
Contributor
Posts: 29

Re: Proc SQL: Combine Multiple Row Values into One

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

IDCount Number of Type
12
Solution
‎11-22-2016 02:00 PM
PROC Star
Posts: 553

Re: Proc SQL: Combine Multiple Row Values into One

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;
PROC Star
Posts: 553

Re: Proc SQL: Combine Multiple Row Values into One

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? Smiley Happy 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 388 views
  • 0 likes
  • 3 in conversation