DATA Step, Macro, Functions and more

Transform multiple ID lines into one

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Transform multiple ID lines into one

[ Edited ]

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

Accepted Solutions
Solution
‎10-31-2017 10:54 AM
Super User
Super User
Posts: 8,081

Re: Transform multiple ID lines into one

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


All Replies
Contributor
Posts: 35

Re: Transform multiple ID lines into one

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
Super User
Posts: 13,512

Re: Transform multiple ID lines into one

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.

Contributor
Posts: 35

Re: Transform multiple ID lines into one

SAS data model
Attachment
Super User
Super User
Posts: 9,599

Re: Transform multiple ID lines into one

As

Super User
Super User
Posts: 8,081

Re: Transform multiple ID lines into one

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;   
Contributor
Posts: 35

Re: Transform multiple ID lines into one

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.
Super User
Super User
Posts: 8,081

Re: Transform multiple ID lines into one


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.

Contributor
Posts: 35

Re: Transform multiple ID lines into one

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.
Solution
‎10-31-2017 10:54 AM
Super User
Super User
Posts: 8,081

Re: Transform multiple ID lines into one

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;
Contributor
Posts: 35

Re: Transform multiple ID lines into one

Dear Tom, You are fantastic/awesome! This worked. Thank very much!!!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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