DATA Step, Macro, Functions and more

Proc SQL: Combine Multiple Row Values into One

Accepted Solution Solved
Reply
Contributor
Posts: 36
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: 768

Re: Proc SQL: Combine Multiple Row Values into One

Posted in reply to Agent1592

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,441

Re: Proc SQL: Combine Multiple Row Values into One

Posted in reply to Agent1592
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: 36

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: 768

Re: Proc SQL: Combine Multiple Row Values into One

Posted in reply to Agent1592

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: 768

Re: Proc SQL: Combine Multiple Row Values into One

Posted in reply to Agent1592

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
  • 539 views
  • 0 likes
  • 3 in conversation