topic Re: sas di studio loop issue in SAS Data Management

sas di studio loop issue

kiranv_ — Tue, 29 Sep 2015 14:33:56 GMT

Hi All

I am trying to do sas di studio loop. Everything works fine but what is happening it is just recording the results from last macro variable as it is overwritting the values from earlier loop value. Please help me how to avoid this problem.

Thanks

Kiran

Re: sas di studio loop issue

arodriguez — Tue, 29 Sep 2015 14:36:13 GMT

A good practice is to define all macro variables (loop variable included) as local to avoid overwrite it with a different macro.
%local i j k;

Re: sas di studio loop issue

LinusH — Tue, 29 Sep 2015 14:55:17 GMT

Can you please specify what you re trying to do, and a bit more concrete of the consequences?

What macro variable, where is defined, and how do you intend to use it (all with respect to inner/outer job).

Re: sas di studio loop issue

kiranv_ — Tue, 29 Sep 2015 14:56:32 GMT

I agree with you. But here I have only one macrovariable that is &st which is derived from a control table.my control table has 3 states values say CA, IN, MI. when I use state="&st". intially all records pertaining to 'CA' is read and then are over written when it comes to 'N' and finally overwritten by 'MI'. After looping I am always having records from state="MI". Hope I have explained my problem clearly. Please let me know if something is unclear.

Re: sas di studio loop issue

kiranv_ — Tue, 29 Sep 2015 15:07:20 GMT

Hi LinusH

I trying to extract data from a table. I am using a control table to extract data from a big table . My extract condition is state="&st"

Control table has 3 states values say CA, IN, MI. when I use state="&st". intially all records pertaining to 'CA' is read and then are over written when it comes to 'IN' and finally overwritten by 'MI'. After looping I am always having records from state="MI" only.

Re: sas di studio loop issue

Reeza — Tue, 29 Sep 2015 15:18:04 GMT

You have to make sure to specify a new dataset each time somehow. Without know more about how you're doing it, its hard to say.

Re: sas di studio loop issue

kiranv_ — Tue, 29 Sep 2015 15:32:23 GMT

Hi Reeza

I am doing a simple DI studio loop job. all I have is a control table and job in which values have to be looped. Please let me know if something is unclear

Re: sas di studio loop issue

LinusH — Tue, 29 Sep 2015 16:42:45 GMT

This is not he question that the macro variable gets overwritten, it's the target table...

If you should store the extract results in the same target table, use Append in the Table Loader. Then make sure to delete/truncate this table before entering the loop (if the target table isn't meant to keep history).

But since it sounds like you are querying the same source table, I can't see the benefit of using a loop. Just build an IN() from your control table (store in a macro variable), and use that in your extract.

Re: sas di studio loop issue

kiranv_ — Tue, 29 Sep 2015 18:36:03 GMT

i want to thank LinusH for your insight. I had replace option setup and I changed it to append. It worked excellently.