I have a table with multiple ID`s(city) I want to create a new one with one line per ID. Some ID's have more than one information in other columns like for Type and K value, For type, I want to create 2 new columns in the final table. One counting how many different types was inside the original data(example: for city G I would like to have a value of and another; column calling that Type as multiple. For K value I just want to have in the final table a value as sum.
City Type k value A House 130 B Building 405 C Apartment 155 D Building 1110 D House 300 E Apartment 230 F Apartment 165 G Building 2500 G House 250 G House 220 H Apartment 185 H House 250 H House 580 H Apartment 570
Try this.
data have ;
length city type $20 kvalue 8;
input city type kvalue;
cards;
A House 130
B Building 405
C Apartment 155
D Building 1110
D House 300
E Apartment 230
F Apartment 165
G Building 2500
G House 250
G House 220
H Apartment 185
H House 250
H House 580
H Apartment 570
;
proc sql ;
create table want as
select city
, count(distinct type) as n_type
, sum(kvalue) as total_kvalue
, case when (calculated n_type=1) then max(type)
else 'Multiple'
end as New_Type
from have
group by 1
;
quit;
proc print;
run;
Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.
Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.
And provide what the result would look like for any example data. So you need not have all the variables and values just enough to demonstrate the complete process.
As
Sounds like a simple SQL query should get what you want.
proc sql;
create table want as
select city
, count(distinct type) as n_type
, sum(kvalue) as total_kvalue
from have
group by 1
;
quit;
@Giovani wrote:
Excellent this worked very well for 2 of the 3 questions. Only one step I still need help For Type. In the column type if there is only one name as house I want a third column as house. If there are more then one observation in type like house and building for the same ID I want to call multiple in the third column.
That is as clear as mud.
Please post what you want as the output dataset. Make sure to use the insert code icon so that the text is not reflowed into paragraphs.
Try this.
data have ;
length city type $20 kvalue 8;
input city type kvalue;
cards;
A House 130
B Building 405
C Apartment 155
D Building 1110
D House 300
E Apartment 230
F Apartment 165
G Building 2500
G House 250
G House 220
H Apartment 185
H House 250
H House 580
H Apartment 570
;
proc sql ;
create table want as
select city
, count(distinct type) as n_type
, sum(kvalue) as total_kvalue
, case when (calculated n_type=1) then max(type)
else 'Multiple'
end as New_Type
from have
group by 1
;
quit;
proc print;
run;
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.