do loop is not working as expected

[Aexor]

%macro generate_fact_csv;

  %put Executing Macro: generate_fact_csv for fact tables;

  %let launchTime = %sysfunc(time());

/* 1. Get internal dim_id based on cdl_global_preference */

 proc sql;

  create table dim_details as

  select 

      t1.parameter_nm,

      t1.parameter_val,

      t2.hierarchy_id,

      t2.internal_dimension_id

   from &planning_lib..cdl_global_preference t1

   inner join &planning_lib..sas_hierarchy t2

   on input(t1.parameter_val, best12.) = t2.hierarchy_id

   and t1.parameter_nm in ('PRODUCT','INTORG','TIME','PROMOTION')

   ;

  quit;

  proc sql;

   select internal_dimension_id into:inter_dim_id separated by ' '

    from dim_details;

 quit;

%put inter_dim_id : &inter_dim_id;

/* 2. get dim_cd - dimesmion names */

 proc sql;

   create table dim_type_id_details as

   select 

      t1.dimension_cd,

      t1.dim_type_id,

      t1.internal_dimension_id

   from &planning_lib..sas_dimension t1

   where  t1.internal_dimension_id in (&inter_dim_id);

   ;

  quit;

  proc sql;

   select dimension_cd into:dim_cd separated by ' '

    from dim_type_id_details;

 quit;

 %put  dimension_cd : &dim_cd;

/* 3. created table from sas_member to get prod,time,location*/

 %do j = 1 %to %sysfunc(countw(&dim_cd));

    %let val = %scan(&dim_cd, &j);

    proc sql;

        create table &val as

        select member_id, member_cd, internal_dimension_id 

        from &planning_lib..sas_member

        where internal_dimension_id = 

              (select internal_dimension_id from &planning_lib..sas_dimension 

                where dimension_cd = "&val") ;

    quit;

   %put dimesnion table created : &val;

 %end;

/* 4. getting Fact table details for which we need to create CSV files */

 proc sql noprint;

     select distinct table_prefix_cd into : tab separated by ' '

        from &planning_lib..cdl_fact_table;

    quit;

 %put tabs value are &tab;

/* loooping throgh Fact table name */

 %do i = 1 %to %sysfunc(countw(&tab));

      %let value = %scan(&tab, &i);

 %if %sysfunc(exist(&planning_lib..&value)) %then %do;

      %put &value Dataset Exists : generating CSV file; 

   /* get table id */

   proc sql noprint;

      select distinct table_id into : tab_id 

        from &planning_lib..cdl_fact_table

         where table_prefix_cd = "&value";

    quit;

   %put table_id is &tab_id ;

  proc sql noprint;

    select distinct column_nm_cd into :column_nm separated by ',' 

    from &planning_lib..cdl_fact_table_column

    where table_id = &tab_id;

    select distinct stg_id_column_nm into :sk_column_nm separated by ' ' 

    from &planning_lib..cdl_fact_table_dimension

    where table_id = &tab_id;

  quit;

  %put column_nm_cd are &column_nm;

  %put sk_column_nm are &sk_column_nm;

 /* extra col end */

 /* check if _dt coloumn available in fact table */

      proc sql noprint;

         select name into :col_list separated by ' '

         from dictionary.columns

         where libname = "%upcase(&planning_lib)"

         and memname = "%upcase(&value)";

     quit;

    %put col_list : &col_list;

  %if %sysfunc(indexw(%upcase(&col_list), DATE_SK)) %then %do;    

      proc sql;

       select distinct date_sk into :date_sk_dis separated by ' '

       from &planning_lib..&value;

     quit;

    %put date_sk_dis = &date_sk_dis;

   %do i = 1 %to %sysfunc(countw(&date_sk_dis));

       %let date_dis = %scan(&date_sk_dis, &i);

          /* Construct your table name */

      %let value_date_dis = &value._date_dis_&date_dis;

       /* create final table */

    proc sql;

     CREATE table &value_date_dis as

      SELECT

        geo.member_cd as geo_id,

        prd.member_cd as prod_id,

        tab.&column_nm

        %if %sysfunc(indexw(%upcase(&sk_column_nm), DATE_ID)) %then %do;

            , time.member_cd as date_id

        %end;

         %if %sysfunc(indexw(%upcase(&col_list), START_DT)) %then %do;

            , tab.start_dt

            ,tab.end_dt        

        %end;

       %if %sysfunc(indexw(%upcase(&sk_column_nm), VEHICLE_ID)) %then %do;

            , vehicle.member_cd as vehicle_id       

        %end;

     FROM

        &planning_lib..&value AS tab

     JOIN

        Geography AS geo ON tab.geo_sk = geo.member_id

     JOIN

        product AS prd ON tab.prod_sk = prd.member_id

     %if %sysfunc(indexw(%upcase(&sk_column_nm), DATE_ID)) %then %do;

    JOIN

        time_new AS time ON tab.date_sk = time.member_id and tab.date_sk = &date_dis

     %end;

     %if %sysfunc(indexw(%upcase(&sk_column_nm), VEHICLE_ID)) %then %do;

    JOIN

        vehicle AS veh ON tab.vehicle_sk = vehicle.member_id

     %end;

    ;

  quit;

 /* Path at which csv files are stored */

  %let path = /path/csv/facts;

  %let valueL = %sysfunc(lowcase(&value));

/*    proc export data= &value */

/*    outfile="&path/ro_&valueL..csv"   */

/*    dbms=csv */

/*    replace; */

/*   run; */

  %end; /* do loop end for date_sk files */

  %end; /* date_sk check */

  %else %do;

   %put will not check for date_sk;

   %end; 

   %end; /* if csv check end */

   %else %do;

    %put &value Dataset does not exists : Not generating CSV file;

   %end;

 %end;  /* do value check end */

  %let landTime = %sysfunc(time());

  %let timeTaken = %sysevalf(&landTime.-&launchTime.);

  %put timetaken: &timeTaken;

%mend generate_fact_csv;

%generate_fact_csv;

Here tabs value are CPRIC COST SALE FCST VEHPE FCSTE PRICE INV, but the loop only checks till SALE .. after that it is not going forward to check for FCST VEHPE FCSTE PRICE INV. Can anyone please tell me what is the issue. 

 %do i = 1 %to %sysfunc(countw(&tab));

      %let value = %scan(&tab, &i);

[LinusH]

I isolated the do-loop/scan, and got it to work:

34         %let dim_cd = CPRIC COST SALE FCST VEHPE FCSTE PRICE INV;
35         %macro  generate_fact_csv;
36         	%do j = 1 %to %sysfunc(countw(&dim_cd));
37         	    %let val = %scan(&dim_cd, &j);
38         		%put &val.;
39         	%end;
40         %mend generate_fact_csv;
41         %generate_fact_csv;
CPRIC
COST
SALE
FCST
VEHPE
FCSTE
PRICE
INV

Suggest that you either try to use macro debugging options like symbolgen, mprint  mlogic, or reducing the macro until this part works.

[Aexor]

This is working , but as I said inside this do loop I have other do loops where I am checking for date_sk.. that portion should iterate also as this Value do loop.

[ballardw]

---

@Aexor wrote:
This is working , but as I said inside this do loop I have other do loops where I am checking for date_sk.. that portion should iterate also as this Value do loop.

---

So, which specific loop is not working as expected?

What specifically is it not doing as expected?

What exactly is expected.

Are you sure you are talking about a %do loop (which is quite different from a Do loop) or a %do /%end block? You have multiple %do and it is not at all clear which is the problem. We do not have any of your data so can't actually run this code. (Hint)

Also you have macro variables such as &planning_lib that we have no idea where/when that is set.

You have multiple select into macro variables that you then iterate over values. If any of the values of the variables placed into those macro variables include any of the %scan delimiters then 1) the Countw over them will be incorrect and 2) values extracted to use one at a time are likely NOT to be valid where you use them. Again, we don't have any of your data to confirm if that might be problem.

Set OPTIONS MPRINT MLOGIC;

Run your macro.

Share the log from running with all the generated code and messages. Copy the log text and paste into a text box on the forum opened using the </> icon above the message window. (Should do the same with CODE as the forum reformats pasted text and code we see may not actually be what you submit)

[Quentin]

That is a lot of code, and without data, it's hard to guess where the problem could be.  But if you're focused on this part:

 %do i = 1 %to %sysfunc(countw(&tab));
      %let value = %scan(&tab, &i);

I would add some %PUT statements to show the value of the macro vars.  Something  like:

%put Before do loop: &=tab;
%do i = 1 %to %sysfunc(countw(&tab));
  %let value = %scan(&tab, &i);
  %put inside do loop: &=i &=value &=tab;

My guess is that will show that the macro variable TAB does not have the value you expect.  It's also possible that there are characters in the value that are being treated as delimiters by %SCAN which you are not expecting.

[yabwon]

You have there:

%do i = 1 %to %sysfunc(countw(&tab));

in line 76

and then you have:

 %do i = 1 %to %sysfunc(countw(&date_sk_dis));

in line 123.

They share the index variable "i".

In your code, where is the %END statement for that first loop in your coed? And where is %END statement for the second?

[Tom]

Let's walk through the code and look for issues.

First thing I see is this INPUT() function call.

   on input(t1.parameter_val, best12.) = t2.hierarchy_id

BEST is the name of a FORMAT. So what you are actually running is:

   on input(t1.parameter_val, 12.) = t2.hierarchy_id

Which will only use the first 12 bytes of PARAMETER_VAL when converting it to a number.  A more robust version would use the maximum width that the informat supports and also remove any leading spaces from the string.

   on input(left(t1.parameter_val), 32.) = t2.hierarchy_id

The next issue is this list of dimension codes you are putting into a macro variable.

   select dimension_cd into :dim_cd separated by ' '

From the way you later use the value you tease back out of the list in DIM_CD it looks like this is a CHARACTER variable.

%let val = %scan(&dim_cd, &j);
...
where dimension_cd = "&val"

What characters can be in the values of DIMENSION_CD?  Could they include spaces?  Or delimiters that %SCAN() would use?  In which case you probably would want to place the quotes into the macro variable and take a little more care when teasing out an individual value from the list.

proc sql noprint;
select quote(trim(dimension_cd),"'") into :dim_cd separated by ' ' 
from dim_type_id_details;
%let n_dim_cd =&sqlobs;
quit;
...
%do j=1 %to &n_dim_cd ;
  %let val = %scan(&dim_cd, &j, %str( ), q);
...
where dimension_cd = &val

The reason your %DO loops are stopping early is because you are changing the values of the iteration variable in the middle of the loop.  If you write code like:

do i=1 to 10;
  i=11;
end;

the loop will only run once and not 10 times

You have at least one place where you have nested a %DO I inside of another %DO I loop.

/* loooping throgh Fact table name */
 %do i = 1 %to %sysfunc(countw(&tab));
...
        %do i = 1 %to %sysfunc(countw(&date_sk_dis));

Use more descriptive loops.

Also can you explain what you are doing with all of this code?  Is there not some way you could just keep the data in datasets an skip the whole process of putting it into macro variables and pulling it back out of macro variables?