topic Re: Nested Loop in SAS Programming

Nested Loop

Myurathan — Tue, 31 Jul 2018 13:40:57 GMT

Hi Experts,

I have following code;


options mprint;
%let no = 1 2 3 4 5 6;

%macro samples (country);

 %let pcoun = %sysfunc(countw(&&country)) ;
 %do i = 1 %to &pcoun;
 %do sta = 1 %to 3;

proc sql outobs=2;
create table selected_&country_&sta_&i as 
select *
from mntly.joined_&country
where n = &i and input(stage,2.) = &sta;

%end;
%end;

%mend;

%samples(no);

I want tables with 2 entries for each portfolio and each stage. it has to create 18 tables. but it only creates 3 tables and stage 2 3 with empty tables.

I get following log

26         options mprint;
27         %macro samples (country);
28         
29         	%let pcoun = %sysfunc(countw(&&country)) ;
30         		%do i = 1 %to &pcoun;
31         			%do sta = 1 %to 3;
32         
33         proc sql outobs=2;
34         create table selected_&country._&sta._&i. as
35         select *
36         from mntly.joined_&country
37         where n = &i and input(stage,2.) = &sta;
38         
39         %end;
40         %end;
41         
42         %mend;
43         
44         %samples(no);
MPRINT(SAMPLES):   proc sql outobs=2;
MPRINT(SAMPLES):   create table selected_no_1_1 as select * from mntly.joined_no where n = 1 and input(stage,2.) = 1;
WARNING: Statement terminated early due to OUTOBS=2 option.
NOTE: Table WORK.SELECTED_NO_1_1 created, with 2 rows and 639 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

MPRINT(SAMPLES):   proc sql outobs=2;
MPRINT(SAMPLES):   create table selected_no_2_1 as select * from mntly.joined_no where n = 1 and input(stage,2.) = 2;
NOTE: Table WORK.SELECTED_NO_2_1 created, with 0 rows and 639 columns.
2                                                          The SAS System                               11:43 Tuesday, July 31, 2018


NOTE: PROCEDURE SQL used (Total process time):
      real time           7.81 seconds
      cpu time            7.59 seconds
      

MPRINT(SAMPLES):   proc sql outobs=2;
MPRINT(SAMPLES):   create table selected_no_3_1 as select * from mntly.joined_no where n = 1 and input(stage,2.) = 3;
NOTE: Table WORK.SELECTED_NO_3_1 created, with 0 rows and 639 columns.

45         
46         GOPTIONS NOACCESSIBLE;
47         %LET _CLIENTTASKLABEL=;
48         %LET _CLIENTPROCESSFLOWNAME=;
49         %LET _CLIENTPROJECTPATH=;
50         %LET _CLIENTPROJECTPATHHOST=;
51         %LET _CLIENTPROJECTNAME=;
52         %LET _SASPROGRAMFILE=;
53         %LET _SASPROGRAMFILEHOST=;
54         
55         ;*';*";*/;quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           7.82 seconds
      cpu time            7.68 seconds
      
55       !                run;

56         ODS _ALL_ CLOSE;
57         
58         
59         QUIT; RUN;
60

Thanks in advance.

Re: Nested Loop

Kurt_Bremser — Tue, 31 Jul 2018 13:08:59 GMT

Your output dataset name is determined solely by the outermost loop, so you will get only one result from the final iteration of the inner loops. Everything else will be overwritten.

You need to somehow incorporate &w and &sta into the dataset name.

Re: Nested Loop

PaigeMiller — Tue, 31 Jul 2018 13:11:10 GMT

Show us the SASLOG! Hint: Don't make us do work that SAS has already done. Hint: don't tell us it doesn't work, explain why it didn't work, what did you expect to get and what did you actually get.

Better yet, place

options mprint;

as the first line of your code, run it again, and then show us the SASLOG. When you are going to paste the SASLOG into your message, click on the {i} icon first, paste the SASLOG into that window.

Re: Nested Loop

Myurathan — Tue, 31 Jul 2018 13:42:37 GMT

I have followed your instruction and changed the code to run for only one country. Still, it is not producing 18 tables as I wanted. It produces only three tables and only one table with two observations.

Re: Nested Loop

gamotte — Tue, 31 Jul 2018 13:51:14 GMT

Hello,

@Kurt_Bremser gave you a good explanation of why you don't get the results

you want.

I am just curious about the following line in your code :

%let pcoun = %sysfunc(countw(&%scan(vlist,&i))) ;

What is it exactly supposed to do ?