Macro Loop skipping first observation in table generation

Reply
Occasional Contributor
Posts: 16

Macro Loop skipping first observation in table generation

I am trying to generate two summary tables and export them to an .xlsx workbook.  The problem I am running into is generating the first summary table for the first observation.  All other tables for other IDs generate and export properly.  When running the specific ID individually it generates the table but not when ran in the macro.  Any suggestions to a remedy for this problem would be greatly appreciated.  Attached is the following macro code where I believe the error is occurring.

%macro runtab(x);

  proc sql;

  CREATE TABLE prov_&x AS

  select distinct BP_Billing_Prov_Num_OSCAR, CHI_TOB_TYPE_OF_BILL_Cd, COUNT (distinct CH_ICN)AS ICN_COUNT, SUM (CLF_AMT_PAID) AS Payment, FP_Facility_Provider_Master_Name

  FROM corf_data

  WHERE BP_Billing_Prov_Num_OSCAR = &x

  GROUP BY CHI_TOB_TYPE_OF_BILL_Cd;

  quit;

proc sql;

  CREATE TABLE prov_&x AS

  select *, SUM (Payment) AS Total FORMAT DOLLAR9.2,

(100*Payment/CALCULATED TOTAL)

AS TOTAL FORMAT=5.2 AS PCT

FROM prov_&x;

quit;

/*Provider Breakout by Revenue Code (Beneficiaries can have more than one revenue service per claim)*/

  proc sql;

  CREATE TABLE prov_revCD_&x AS

  select distinct BP_Billing_Prov_Num_OSCAR,CLI_REVENUE_Cd , COUNT (distinct BENE_CLAIM_HIC_Num)AS HIC_COUNT, SUM (CLF_AMT_PAID) AS Payment

  FROM corf_data

  WHERE BP_Billing_Prov_Num_OSCAR = &x

  GROUP BY CLI_REVENUE_Cd;

  quit;

proc sql;

  CREATE TABLE prov_revCD_&x AS

  select *, SUM (Payment) AS Total FORMAT DOLLAR9.2,

(100*Payment/CALCULATED TOTAL)

AS TOTAL FORMAT=5.2 AS PCT

FROM prov_revCD_"&x";

quit;

%mend runtab;

PROC Star
Posts: 7,492

Re: Macro Loop skipping first observation in table generation

Posted in reply to bradbelf262

Difficult to answer without knowing more.

Can you post the macro call that isn't working, along with a couple of the ones that are working?

Art

Occasional Contributor
Posts: 16

Re: Macro Loop skipping first observation in table generation

Yes, sorry I should have included that information originally.

/*For each ID number generate two tables-provider breakout by TOB and by Revenue Code*/

%macro runtab(x);

/*Provider Breakout by TOB for Paid Claims*/

  proc sql;

  CREATE TABLE prov_&x AS

  select distinct BP_Billing_Prov_Num_OSCAR, CHI_TOB_TYPE_OF_BILL_Cd, COUNT (distinct CH_ICN)AS ICN_COUNT, SUM (CLF_AMT_PAID) AS Payment, FP_Facility_Provider_Master_Name

  FROM corf_data

  WHERE BP_Billing_Prov_Num_OSCAR = &x

  GROUP BY CHI_TOB_TYPE_OF_BILL_Cd;

  quit;

proc sql;

  CREATE TABLE prov_&x AS

  select *, SUM (Payment) AS Total FORMAT DOLLAR9.2,

(100*Payment/CALCULATED TOTAL)

AS TOTAL FORMAT=5.2 AS PCT

FROM prov_&x;

quit;

/*Provider Breakout by Revenue Code*/

  proc sql;

  CREATE TABLE prov_revCD_&x AS

  select distinct BP_Billing_Prov_Num_OSCAR,CLI_REVENUE_Cd , COUNT (distinct BENE_CLAIM_HIC_Num)AS HIC_COUNT, SUM (CLF_AMT_PAID) AS Payment

  FROM corf_data

  WHERE BP_Billing_Prov_Num_OSCAR = &x

  GROUP BY CLI_REVENUE_Cd;

  quit;

proc sql;

  CREATE TABLE prov_revCD_&x AS

  select *, SUM (Payment) AS Total FORMAT DOLLAR9.2,

(100*Payment/CALCULATED TOTAL)

AS TOTAL FORMAT=5.2 AS PCT

FROM prov_revCD_"&x";

quit;

%mend runtab;

/*Create a macro variable of all the IDcodes */

proc sql noprint;

  select BP_Billing_Prov_Num_OSCAR

  into :varlist separated by ' ' /*Each OSCAR code in the list is sep. by a single space*/

from provider;

quit;

%let cntlist = &sqlobs; /*Store a count of the number of oscar codes*/

%put &varlist; /*Print the codes to the log to be sure our list is accurate*/

/*write a macro to generate the output tables*/

%macro output(x);

ods tagsets.excelxp options(sheet_interval='none' sheet_name="&x");

proc print data=prov_&x;

run;

proc print data=prov_revcd_&x;

run;

%mend;

/*Run a loop for each IDcode. Each code will enter the document generation loop*/

%macro loopit(mylist);

    %let else=;

   %let n = %sysfunc(countw(&mylist)); /*let n=number of codes in the list*/

    data

   %do I=1 %to &n;

      %let val = %scan(&mylist,&I); /*Let val= the ith code in the list*/

    %end;

   %do j=1 %to &n;

      %let val = %scan(&mylist,&j); /*Let val= the jth code in the list*/

/*Run the macro loop to generate the required tables*/

%runtab(&val);

%output(&val);

   %end;

   run;

%mend;

/*Run the macro loop over the list of significant procedure code values*/

ods tagsets.excelxp file="C:\USER\test2015.xml";

%loopit(&varlist)

ods tagsets.excelxp close;

This is the first error:

1        proc sql;     CREATE TABLE prov_&x AS     select distinct BP_Billing_Prov_Num_OSCAR,

                       ------

                       180

1   !  CHI_TOB_TYPE_OF_BILL_Cd, COUNT (distinct CH_ICN)AS ICN_COUNT, SUM (CLF_AMT_PAID) AS

1   ! Payment, FP_Facility_Provider_Master_Name     FROM corf_data     WHERE

ERROR 180-322: Statement is not valid or it is used out of proper order.

HERE the second table for the first ID is being created:

NOTE: The query requires remerging summary statistics back with the original data.

NOTE: Table WORK.PROV_REVCD_144527 created, with 1 rows and 4 columns.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.04 seconds

      cpu time            0.04 seconds

AND here is another error code which I am assuming is preventing the first table from generating

NOTE: Line generated by the macro variable "X".

1     "144527

      -------

      22

      76

ERROR: File WORK.PROV_144527.DATA does not exist.

ERROR 22-322: Syntax error, expecting one of the following: ',', GROUP, HAVING, ORDER,

              WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

AND here is log output for working IDs

NOTE: Table WORK.PROV_164503 created, with 4 rows and 7 columns.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

NOTE: The query requires remerging summary statistics back with the original data.

NOTE: Table WORK.PROV_REVCD_164503 created, with 9 rows and 4 columns.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.07 seconds

      cpu time            0.06 seconds

Super User
Posts: 11,343

Re: Macro Loop skipping first observation in table generation

Posted in reply to bradbelf262

Run your code with options mprint symbolgen;

I think the issue is that your LOOPIT macro generates the start of a datastep that looks like:

Data var1 var2 var3 var1 var2 var3   /* assuming the value of varlist would be: var1 var2 var3.*/

which then encounters the %runtab and starts with

Proc sql;

NOTE the the datastep code above has several issues but the error is the data step now looks like;

Data var1 var2 var3 var1 var2 var3  proc sql;

create table;  <and this is not valid for a data step hence ERROR 180-322: Statement is not valid or it is used out of proper order.>

Create would be okay if followed by = or + but not as stated.

You are missing at least one ; to close the data statement. Then there are no actual statements to assign or create variables before getting into the runtab macro.

Did you have a program that worked before writing this macro code?

Occasional Contributor
Posts: 16

Re: Macro Loop skipping first observation in table generation

Hey yes have, I actually got it working by just setting I and J equal to 0.  I am still getting the 180-322 error in my log but it is generating the output, that it would if I were to run every ID individually. I will attempt to make your suggested changes and see if that gets rid of the error messages in the log. Thank you for the suggestion.

Occasional Contributor
Posts: 16

Re: Macro Loop skipping first observation in table generation

So I am generating the output I want but still generating errors in my log due to the aforementioned comments above.  Would adding something like a DOSUBL with the CALL EXECUTE routine get rid of these or would it be best to re-order by code?

Super User
Posts: 11,343

Re: Macro Loop skipping first observation in table generation

Posted in reply to bradbelf262

In your current code:

%macro loopit(mylist);

    %let else=;

   %let n = %sysfunc(countw(&mylist)); /*let n=number of codes in the list*/

    data

   %do I=1 %to &n;

      %let val = %scan(&mylist,&I); /*Let val= the ith code in the list*/

    %end;

/* you need a ; to end the data definition statement so */

    ;

/* and when working with MACRO code it is a good idea to be very explicit about where data

steps or procs end with a run or quit statement as appropriate. */

/*however I have no idea why you a calling a data step here at all as the next bit
goes directly into proc sql. It appears to attempt to create datasets with no observations or variables.
It may be that the above should be removed */

   %do j=1 %to &n;

      %let val = %scan(&mylist,&j); /*Let val= the jth code in the list*/

/*Run the macro loop to generate the required tables*/

%runtab(&val);

%output(&val);

   %end;

   run;

%mend;

PROC Star
Posts: 7,492

Re: Macro Loop skipping first observation in table generation

Posted in reply to bradbelf262

The first problem I see is that you are mixing some things that can't be mixed.

You have a macro that creates and runs a data step but, within that data step, you are calling another macro that is attempting to run some proc sql calls from within the data step. Won't work!

Ask a Question
Discussion stats
  • 7 replies
  • 378 views
  • 0 likes
  • 3 in conversation