append data vertically

Reply
Frequent Contributor
Posts: 129

append data vertically

Hi

I have 2 tables (tbl_forecast and tbl_actual) and I want to append the data vertically

Here are my issues with Tbl_actual

  1. This table has different column names than tbl_forecast
  2. gets the column added dynamically based on the month, so let’s say if I run it today it will have columns from Jan to April but when I’ll run it in May then it will add a new column for May

Here is a sample tbl_forecast

Group   City            JAN13   FEB13    MAR13 APR13

F 2013   Toronto      5            10           7              15

F 2012   Toronto      6            8             11            15

Here is the sample tbl_actual

Group   City                        2013-1   2013-2

Actual   Toronto                     4              11

Here is the output that I want to show

Group   City                       JAN13   FEB13    MAR13 APR13

F 2013   Toronto                5              10           7            15

F 2012   Toronto                6             8             11           15

Actual   Toronto                4              11          -               -

Please advice

Thanks

Regular Contributor
Posts: 220

Re: append data vertically

You may need to simply rename the TBL_ACTUAL variable names for two reasons: 1) you can then set the two data sets, and 2) 2013-1 is not a valid SAS variable name (must start with either a letter or underscore).  I changed the 2013-1 to d2013_1 for you.  Here is a possible solution.

data tbl_forecast;

      input Group $ City $ JAN13 FEB13 MAR13 APR13;

      cards;

F2013   Toronto 5            10           7              15

F2012   Toronto 6            8             11            15

;

run;

data tbl_actual;

      input Group $ City $ d2013_1 d2013_2;

      cards;

Actual   Toronto                     4              11

;

run;

proc sql;

     select count(name) into :numforecast from sashelp.vcolumn where libname="WORK" & memname="TBL_FORECAST" & name not in ("Group","City");

     %let numforecast=%sysfunc(compress(&numforecast.));

     select name into :forecast1-:forecast&numforecast. from sashelp.vcolumn where libname="WORK" & memname="TBL_FORECAST" & name not in ("Group","City") order by varnum;

     select count(name) into :numactual from sashelp.vcolumn where libname="WORK" & memname="TBL_ACTUAL" & name not in ("Group","City");

     %let numactual=%sysfunc(compress(&numactual.));

     select name into :actual1-:actual&numactual. from sashelp.vcolumn where libname="WORK" & memname="TBL_ACTUAL" & name not in ("Group","City") order by varnum;

quit;

%macro rename();

      %global rename;

      %let loop=%sysfunc(min(&numforecast.,&numactual.));

      %let rename=%str(&actual1.=&forecast1.);

      %do i=2 %to &loop.;

            %let rename=&rename. %str(&&actual&i.=&&forecast&i.);

      %end;

      %put &rename.;

%mend rename;

%rename();

data want;

     set tbl_forecast tbl_actual (rename=(&rename.));

run;

Super Contributor
Posts: 644

Re: append data vertically

There may be a simpler solution using the UNION operator in SQL, which does not require column names to be the same:

"Columns are appended by position in the tables, regardless of the column names. However, the data type of the corresponding columns must match or the union will not occur."

Proc SQL ;

     Create table want as

          Select *

          From tbl_forecast

               UNION

          Select *

          From tbl_actual

          Order by city

               ,     Substr(group, 3, 6)

     ;

Quit


(untested code - I don't know whether the order by clause would work)


Richard


Message was edited by: Richard Carson Include city in the order clause If the cities have to be in a particular order this could be managed by introducing a column with the city order in each select, based on a suitable informat for city.

Ask a Question
Discussion stats
  • 2 replies
  • 217 views
  • 0 likes
  • 3 in conversation