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

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

Eva — Thu, 21 Nov 2013 17:05:50 GMT

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

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

PaigeMiller — Thu, 21 Nov 2013 17:21:03 GMT

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

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

Peter_C — Thu, 21 Nov 2013 17:25:09 GMT

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

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

Reeza — Thu, 21 Nov 2013 17:26:59 GMT

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;

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

Eva — Mon, 25 Nov 2013 13:44:21 GMT

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 😞

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

Tal — Mon, 25 Nov 2013 15:01:58 GMT

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

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

Tom — Mon, 25 Nov 2013 15:32:08 GMT

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;

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

data_null__ — Mon, 25 Nov 2013 15:47:28 GMT

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

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

Robertguo — Mon, 25 Nov 2013 15:48:02 GMT

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;