sas di studio loop issue

[kiranv_]

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

[arodriguez]

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;

[kiranv_]

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.

[LinusH]

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).

[kiranv_]

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.

[Reeza]

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.

[kiranv_]

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

[LinusH · Accepted solution]

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.

[kiranv_]

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