topic Re: Transform multiple ID lines into one in SAS Programming

Transform multiple ID lines into one

Giovani — Tue, 31 Oct 2017 14:04:24 GMT

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

Re: Transform multiple ID lines into one

Giovani — Tue, 31 Oct 2017 13:38:42 GMT

Re: Transform multiple ID lines into one

ballardw — Tue, 31 Oct 2017 13:58:42 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Transform multiple ID lines into one

RW9 — Tue, 31 Oct 2017 14:03:35 GMT

Re: Transform multiple ID lines into one

Tom — Tue, 31 Oct 2017 14:09:15 GMT

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;

Re: Transform multiple ID lines into one

Giovani — Tue, 31 Oct 2017 14:12:09 GMT

SAS data model

Re: Transform multiple ID lines into one

Giovani — Tue, 31 Oct 2017 14:21:38 GMT

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.

Re: Transform multiple ID lines into one

Tom — Tue, 31 Oct 2017 14:27:31 GMT

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

Re: Transform multiple ID lines into one

Giovani — Tue, 31 Oct 2017 14:42:46 GMT

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.

Re: Transform multiple ID lines into one

Tom — Tue, 31 Oct 2017 14:50:25 GMT

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;

Re: Transform multiple ID lines into one

Giovani — Tue, 31 Oct 2017 14:55:52 GMT

Dear Tom, You are fantastic/awesome! This worked. Thank very much!!!