BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kiranv_
Rhodochrosite | Level 12

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

8 REPLIES 8
arodriguez
Lapis Lazuli | Level 10
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_
Rhodochrosite | Level 12

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
Tourmaline | Level 20

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
kiranv_
Rhodochrosite | Level 12

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
Super User
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_
Rhodochrosite | Level 12

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
Tourmaline | Level 20

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
kiranv_
Rhodochrosite | Level 12

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

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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