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 |
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;
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 |
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;
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? 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.