Add information to an existing dataset in SAS

[tafteh]

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,

[ballardw]

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.

[tafteh]

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,

[Shmuel · Accepted solution]

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;

[tafteh]

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

[Shmuel]

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

[Tom]

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;