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? 🙂
⏰
Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.
Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.