BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Giovani
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

10 REPLIES 10
Giovani
Obsidian | Level 7
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. ADDED SAMPLE DATA
ballardw
Super User

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.

Giovani
Obsidian | Level 7
SAS data model
RW9
Diamond | Level 26 RW9
Diamond | Level 26

As

Tom
Super User Tom
Super User

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
Obsidian | Level 7
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.
Tom
Super User Tom
Super User

@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.

Giovani
Obsidian | Level 7
I want to keep the column Type. If the City has only one information in the column Type I want to keep that information for Type. If the City has more than one information in the column Type I want to call Type as multiple.
Tom
Super User Tom
Super User

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;
Giovani
Obsidian | Level 7
Dear Tom, You are fantastic/awesome! This worked. Thank very much!!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1893 views
  • 0 likes
  • 4 in conversation