topic Re: Add information to an existing dataset in SAS in SAS Programming

Add information to an existing dataset in SAS

tafteh — Mon, 29 Aug 2016 20:17:57 GMT

Hi,

I have a dataset in SAS which includes person_id, and the country.

Person_id, Country

1 USA

2 USA

3, GER

4, DEN

How can I add the continent info to its current data set? can I do it using macro? so the resulting data set be like the following:

Person_id, Country, Continent

1 USA NORTH AMERICA

2 USA NORTH AMERICA

3, GER Europe

4, DEN Europe

Thanks,

Re: Add information to an existing dataset in SAS

ballardw — Mon, 29 Aug 2016 20:25:40 GMT

Do you have that content information in a data set? Especially if it has the contry name/abbreviation, then an SQL join would possibly be the easiest. If not, then building such a set may be the first thing to do.

Re: Add information to an existing dataset in SAS

Shmuel — Mon, 29 Aug 2016 20:31:23 GMT

you don't need a macro to do it;

you can add:

select (country) ;

when ('USA') continent = 'NORTH AMERICA';

when ('GER' 'DEN') continent = 'EUROPE';

otherwize continent = 'OTHER';

end;

or you can do it by creating a format to get the continent of the country:

proc format lib=worl;

value $continent

'USA' = ' NORTH AMERICA'

'GER' = 'EUROPE'

'DEN' = 'EUROPE'

...

otehr = 'Not Defined'

;

run;

data WANT;

set HAVE;

length continent $25;

continent = putc(country, $continent.);

run;

Re: Add information to an existing dataset in SAS

tafteh — Mon, 29 Aug 2016 20:31:28 GMT

Thanks for your anaswer ballardw,

is there anychance I could have sample SAS code of the solution? I think one approach is to create a reference table/data set to hold countries and the continent. Then using the a macro, each row of the current data_set (person_ID and country) being fed to and withing the macro, the continent info is fetched, and fanially being added to the current data set as the new column/variable. Can you help me with the sample code of the above solution?

Thanks,

Re: Add information to an existing dataset in SAS

tafteh — Mon, 29 Aug 2016 20:33:41 GMT

Thanks for the code. I believe the "WANT" data is the data holding the continent. is that correct?

Re: Add information to an existing dataset in SAS

Shmuel — Mon, 29 Aug 2016 23:58:05 GMT

That's right - WANT is rhe output dataset with the new variable CONTINENT;

Re: Add information to an existing dataset in SAS

Tom — Tue, 30 Aug 2016 00:22:15 GMT

This is not a problem where macro code would be very useful. You do not need to dynamically generate SAS code. You just need to use normal SAS statements to manipulate your data. First let's make dataset out of your example data.

data have;
  input person_id country $ ;
cards;
1 USA
2 USA
3 GER
4 DEN
;

data country2continent ;
  input country $ continent $20. ;
cards;
USA NORTH AMERICA
GER EUROPE
DEN EUROPE
;

Then we can combine them. For example by using an SQL join.

proc sql ;
  create table want as 
   select a.*,b.continent
   from have a 
   left join country2continent b
   on a.country = b.country
 ;
quit;