BookmarkSubscribeRSS Feed
Eva
Quartz | Level 8 Eva
Quartz | Level 8

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

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Peter_C
Rhodochrosite | Level 12

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

Reeza
Super User

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;

Eva
Quartz | Level 8 Eva
Quartz | Level 8

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 😞

data_null__
Jade | Level 19

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

Tom
Super User Tom
Super User

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;

Robertguo
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1214 views
  • 0 likes
  • 8 in conversation