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? 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.