Vertically joining data in two (or more) data sets using proc sql. If each data set represents data gathered in different years data_2000, data_2001, data_2002, ...

If one wants to take a column from each file, but add a column populated with the year each row was taken from, creating the column is easy enough, but I don't know how to populate the values for each row.

I'd like to use a macro, give a list of files and years, let the macro generate the proc sql code to join given columns, et cetera

asked 17 Aug '11, 16:55

jcress's gravatar image

accept rate: 0%

The basic approach I'd suggest is to assign a constant representing the year in each of the select queries ...

proc sql;
    create table data_all as    
        select 2000 as year, *
        from data_2000
        UNION ALL
        select 2001 as year, *
        from data_2001
        UNION ALL
        select 2002 as year, *
        from data_2002

You can then turn this into a macro as follows:

%macro join_data(dat=);
    proc sql;
        create table data_all as
            %let i = 1;
            %let cur_dat = %scan(&dat.,1);
            %do %while (&cur_dat. ne);
                %if &i. > 1 %then UNION ALL;
                select &cur_dat. as year, *
                from data_&cur_dat.
                %let i = %eval(&i. + 1);
                %let cur_dat = %scan(&dat.,&i.);
%mend join_data;
%join_data(dat=2000 2001 2002);

answered 18 Aug '11, 10:38

WilliamDobson's gravatar image

accept rate: 36%

edited 18 Aug '11, 10:39

Thanks Will. (And sorry it's taken me so long to comment back. It seems it's been so long that RunSubmit doesn't even want me to sign in anymore, so I had to start new.) Weirdly, even though SAS seems to acknowledge the filename statement because I get the filename I specify in it, the LRECL statement appears to be wow gold skipped.


answered 17 Oct '11, 06:15

different's gravatar image

accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 17 Aug '11, 16:55

Seen: 5,031 times

Last updated: 17 Oct '11, 06:15

powered by OSQA