SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

sas di studio loop issue

Accepted Solution Solved
Reply
PROC Star
Posts: 252
Accepted Solution

sas di studio loop issue

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

 

 


Accepted Solutions
Solution
‎09-29-2015 02:06 PM
Super User
Posts: 5,256

Re: sas di studio loop issue

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.

Data never sleeps

View solution in original post


All Replies
Frequent Contributor
Posts: 144

Re: sas di studio loop issue

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;
PROC Star
Posts: 252

Re: sas di studio loop issue

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.

Super User
Posts: 5,256

Re: sas di studio loop issue

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

Data never sleeps
PROC Star
Posts: 252

Re: sas di studio loop issue

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.

Super User
Posts: 17,829

Re: sas di studio loop issue

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.
PROC Star
Posts: 252

Re: sas di studio loop issue

[ Edited ]

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

 

Solution
‎09-29-2015 02:06 PM
Super User
Posts: 5,256

Re: sas di studio loop issue

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.

Data never sleeps
PROC Star
Posts: 252

Re: sas di studio loop issue

[ Edited ]

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 470 views
  • 1 like
  • 4 in conversation