DATA Step, Macro, Functions and more

add rows to an existing data file (mod like in file statement possible?)

Reply
Regular Contributor
Regular Contributor
Posts: 156

add rows to an existing data file (mod like in file statement possible?)

Dear all,

I have a macro %do iteration which contains a data step:

%let mylist = 123:444:647;

%do i=1 to 3 by 1;

     %let var_i = %scan(&mylist,&i,":");

     proc sql noprint;

          select organisation :into org_number separated by "," from report.organisations where xy=&var_i;

     quit;

     data work.myfullresult;

          set report.mybigdata;

          where org in (org_number);

          my_new_var = %var_i;

          drop my_old_var;

     run;

%end;

Now the file work.myfullresult contains only the rows for the last %do iteration because the last work.myfullresult overwrites the one before.

My aim however is to have all rows of all iterations in one final data set.

This reminds me of the mod (modify) attribute for the file statement which add news rows to the existing file.

Is there anything like that for the data step?

Best regards

Eva

Trusted Advisor
Posts: 1,931

Re: add rows to an existing data file (mod like in file statement possible?)

PROC APPEND will work

Also, you could rewrite the SQL to pull all organisation numbers from all values in &mylist instead of just those where xy=&var_i

Valued Guide
Posts: 2,177

Re: add rows to an existing data file (mod like in file statement possible?)

Have you considered PROC APPEND?

The mod/append functionality in a datastep  that corresponds to FILE statement MOD option would the the OUTPUT statement in a data step writing a dataset with a MODIFY statement.

I think the PROC APPEND approach is easier to code. If performance is an issue for large data, then instead of creating a dataset create a view. Then the data are created as the PROC APPEND runs.

peter.c

Super User
Posts: 19,855

Re: add rows to an existing data file (mod like in file statement possible?)

Proc append is probably what you're looking for:

And i think org_number in your query should be &org_number. and %var_i should be &var_i

%let mylist = 123:444:647;

%do i=1 to 3 by 1;

*remove old version of final file if it exists;

proc sql;

drop table final_results;

quit;

     %let var_i = %scan(&mylist,&i,":");

     proc sql noprint;

          select organisation :into org_number separated by "," from report.organisations where xy=&var_i;

     quit;

     data work.myfullresult;

          set report.mybigdata;

          where org in (&org_number);

          my_new_var = &var_i;

          drop my_old_var;

     run;

proc append base=final_results data=myfullresult;

run;

%end;

Regular Contributor
Regular Contributor
Posts: 156

Re: add rows to an existing data file (mod like in file statement possible?)

Thanks a lot for your great feedback! I solved it with proc append.

What a pity that there is no "modify" for the data step :-(

Respected Advisor
Posts: 3,799

Re: add rows to an existing data file (mod like in file statement possible?)

There is MODIFY for data step and you can use it to APPEND records among other things.

Super Contributor
Super Contributor
Posts: 444

Re: add rows to an existing data file (mod like in file statement possible?)

can anyone pls tell me what 123:444:647 means?

Super User
Super User
Posts: 7,074

Re: add rows to an existing data file (mod like in file statement possible?)

Perhaps your real problem is more complex, but why are you looping when you could pull all of the records in one query instead?

%let mylist = 123,444,647;

proc sql noprint ;

  create table work.myfullresults as

  select a.*,b.organisation as my_new_var

  from report.mybigdata a

     , report.organization b

  where a.org = b.organisation

    and b.xy in (&mylist)

  ;

quit;

N/A
Posts: 1

Re: add rows to an existing data file (mod like in file statement possible?)

I guess Tom's answer is a great one. There is really no need to iterate in this case.  If you insist using iteration as you did, you can either use proc append, the SQL Drop mentioned in Reeza should be outside the loop and you might have a warning if the dataset was not exist at first!

And please let me point out something (no offense):

%let mylist = 123:444:647;

%do i=1 to 3 by 1;  /*(%to %by??)*/

     %let var_i = %scan(&mylist,&i,":"); /* (:, no need for " ")*/

     proc sql noprint;

          select organisation :into org_number separated by "," from report.organisations where xy=&var_i; /*(do you need " " here??)*/

     quit;

     data work.myfullresult;

          set report.mybigdata;

          where org in (org_number);

          my_new_var = %var_i; /*&var_i*/

          drop my_old_var;

     run;

%end;

Ask a Question
Discussion stats
  • 8 replies
  • 371 views
  • 0 likes
  • 8 in conversation