topic Re: Export based on condition of dataset in SAS Programming

Export based on condition of dataset

JC411911 — Thu, 28 Mar 2024 17:22:34 GMT

Hello,

I have a dataset that I need to export based on a condition and can’t get it to work. I have the below code that runs but doesn’t export the right dataset. When the variable case_nr has a value for any observation then I want to export just those observations else export the whole dataset. It seems simple enough but when I test it my dataset has a record that has a value in the field case_nr it still exports everything instead of just that observation. Likewise, I would expect to receive the full dataset when none of the observations have a value in case_nr.

DATASET: ME.ALL_MEM_BKS

ACCT	NUMBER	TYPE	AMOUNT	CASE_NR	ADDR	ADDR1	ADDR2
123456	123	PRIMARY	1.25		123	LANE
185795	456	PRIMARY	3.95	987132	43	DRIVE
987562	789	PRIMARY	2.85		924	STREET

PROC SQL NOPRINT;
    SELECT COUNT(*) INTO :MISSINGCOUNT
    FROM ME.ALL_MEM_BKS
    WHERE CASE_NR = '';
QUIT;

%IF &MISSINGCOUNT > 0 %THEN %DO;
    %PUT &MISSINGCOUNT;

    %TCPWIN;
    %SYSLPUT BK_PATH = &BK_PATH;
    %SYSLPUT ISSUE = &ISSUE;

    RSUBMIT;
        %LET BKFILE = ISSUE_&ISSUE._BANKRUPTCY;
        PROC EXPORT DATA=ME.ALL_MEM_BKS
            OUTFILE="&BK_PATH\&BKFILE..XLSX"
            DBMS = XLSX REPLACE;
            SHEET="BK_FILE";
        RUN;
    ENDRSUBMIT;
    SIGNOFF;

%END;
%ELSE %DO;
    %PUT &MISSINGCOUNT;

    PROC SQL NOPRINT;
        CREATE TABLE ALL_MEM_BK1 AS
        SELECT *
        FROM ME.ALL_MEM_BKS
        WHERE CASE_R IS NOT NULL;
    QUIT;

    %IF %SYSFUNC(EXIST(ALL_MEM_BK1)) %THEN %DO;
        %TCPWIN;
        %SYSLPUT BK_PATH = &BK_PATH;
        %SYSLPUT ISSUE = &ISSUE;

        RSUBMIT;
            %LET BKFILE = ISSUE_&ISSUE._BANKRUPTCY;
            PROC EXPORT DATA=ALL_MEM_BK1
                OUTFILE="&BK_PATH\&BKFILE..XLSX"
                DBMS = XLSX REPLACE;
                SHEET="BK_FILE";
            RUN;
        ENDRSUBMIT;
        SIGNOFF;
    %END;

Re: Export based on condition of dataset

PaigeMiller — Thu, 28 Mar 2024 17:42:20 GMT

What value do you get from %put &missingcount?

Re: Export based on condition of dataset

ballardw — Thu, 28 Mar 2024 17:42:44 GMT

Show your log. Copy the log and then paste that into a text box.

Since you are apparently using macros you should set OPTIONS MPRINT; so the log shows details of what goes on inside the macro.

Your shown code is missing at least one %end;

I don't see anything shown in your code that attempts to filter the data set to just the observations where that variable isn't missing.Did you perhaps intend:

%IF &MISSINGCOUNT > 0 %THEN %DO;
    %PUT &MISSINGCOUNT;

    %TCPWIN;
    %SYSLPUT BK_PATH = &BK_PATH;
    %SYSLPUT ISSUE = &ISSUE;

    RSUBMIT;
        %LET BKFILE = ISSUE_&ISSUE._BANKRUPTCY;
        PROC EXPORT DATA=ME.ALL_MEM_BKS (where=(not missing(case_nr)))
            OUTFILE="&BK_PATH\&BKFILE..XLSX"
            DBMS = XLSX REPLACE;
            SHEET="BK_FILE";
        RUN;
    ENDRSUBMIT;
    SIGNOFF;

%END;

Re: Export based on condition of dataset

Tom — Thu, 28 Mar 2024 18:47:04 GMT

When the variable case_nr has a value for any observation then I want to export just those observations else export the whole dataset. It seems simple enough ...

Yes. So why not just take the simple approach.

Let's first IGNORE all of your complications of RSUBMIT blocks since that has NOTHING to do with the question.

Let's forget any macro logic, as it is not needed. Instead we just need one macro variable.

Which we can create with this simple data step. It will either have nothing or it will have a WHERE= dataset option.

data _null_;
  call symputx('where',' ');
  set ME.ALL_MEM_BKS;
  where not missing(case_nr);
  call symputx('where','where=(not missing(case_nr))');
  stop;
run;

We can now use the WHERE macro variable in the PROC EXPORT code:

PROC EXPORT DATA=ME.ALL_MEM_BKS(&where)
    OUTFILE="&BK_PATH\&BKFILE..XLSX"
    DBMS = XLSX REPLACE
;
    SHEET="BK_FILE";
RUN;

If you need to you can wrap both steps into your RSUBMIT/ENDRSUBMIT block.

Re: Export based on condition of dataset

JC411911 — Thu, 28 Mar 2024 18:53:36 GMT

I attempted to run this code but got an invalid option name &.

PROC EXPORT DATA=ME.ALL_MEM_BKS (&where)
-
22
ERROR 22-7: Invalid option name &.

I changed the _null_ to write the dataset for testing purposes and it shows no results although I would think it would have something as there are conditions for both with a value and without in my dataset.

data Test;
  call symputx('where',' ');
  set ME.ALL_MEM_BKS;
  where not missing(case_nr);
  call symputx('where','where=(not missing(case_nr))');
  stop;
run;

%TCPWIN;
    %SYSLPUT BK_PATH = &BK_PATH;
    %SYSLPUT ISSUE = &ISSUE;
    RSUBMIT;
        %LET BKFILE = ISSUE_&ISSUE._BANKRUPTCY;
        PROC EXPORT DATA=ME.ALL_MEM_BKS (&where)
            OUTFILE="&BK_PATH\&BKFILE..XLSX"
            DBMS = XLSX REPLACE;
            SHEET="BK_FILE";
        RUN;
    ENDRSUBMIT;
    SIGNOFF;

Re: Export based on condition of dataset

Tom — Thu, 28 Mar 2024 19:03:25 GMT

You told FRED to define the WHERE macro variable. You then asked SAM to use the value of the WHERE macro variable that you never created.

Move the data step to the place where it can be useful. Does the dataset even exist in the other SAS session?

%TCPWIN;
%SYSLPUT BK_PATH = &BK_PATH;
%SYSLPUT ISSUE = &ISSUE;
RSUBMIT;
%LET BKFILE = ISSUE_&ISSUE._BANKRUPTCY;

data Test;
  call symputx('where',' ');
  set ME.ALL_MEM_BKS;
  where not missing(case_nr);
  call symputx('where','where=(not missing(case_nr))');
  stop;
run;

PROC EXPORT DATA=ME.ALL_MEM_BKS (&where)
    OUTFILE="&BK_PATH\&BKFILE..XLSX"
    DBMS = XLSX REPLACE
;
    SHEET="BK_FILE";
RUN;

ENDRSUBMIT;
SIGNOFF;

Re: Export based on condition of dataset

JC411911 — Thu, 28 Mar 2024 19:12:13 GMT

That worked thank you for the explanation and simplifying the code. I had my macros being called in the middle rather than before my data steps. I was definitely over complicating it before. I tested with two different audiences and the results are as expected.

Re: Export based on condition of dataset

JC411911 — Thu, 28 Mar 2024 20:38:03 GMT

I know this is asking a lot but is it possible to use this same step but if the output is all only export certain variables but all the observations?

Re: Export based on condition of dataset

Tom — Thu, 28 Mar 2024 21:20:00 GMT

Depends on what you mean by that. There is also the KEEP= or DROP= dataset options that can be used to control what variables get into the PROC EXPORT step.

Re: Export based on condition of dataset

JC411911 — Thu, 28 Mar 2024 22:05:07 GMT

So ultimately I would like to keep only acct and member if there are no records in case_nr and all variables and observations if there are records in case_nr. I tried to add them to a keep statement in addition to &where in the proc export step but it didn’t output just those.

Re: Export based on condition of dataset

Tom — Thu, 28 Mar 2024 23:38:06 GMT

@JC411911 wrote:
So ultimately I would like to keep only acct and member if there are no records in case_nr and all variables and observations if there are records in case_nr. I tried to add them to a keep statement in addition to &where in the proc export step but it didn’t output just those.

What did you try?

data Test;
  call symputx('dsoptions','keep=acct member');
  set ME.ALL_MEM_BKS;
  where not missing(case_nr);
  call symputx('dsoptions','where=(not missing(case_nr))');
  stop;
run;

PROC EXPORT DATA=ME.ALL_MEM_BKS (&dsoptions)
    OUTFILE="&BK_PATH\&BKFILE..XLSX"
    DBMS = XLSX REPLACE
;
    SHEET="BK_FILE";
RUN;

Re: Export based on condition of dataset

JC411911 — Fri, 29 Mar 2024 01:43:27 GMT

@Tom I appreciate all your help that is exactly what I was looking for. I was trying to use the keep statement in the export step. I also tried using the keep statement in the set and where and that obviously didnt work. I took that out and added it to the call statement and it worked. Thanks again!

data _NULL_;
call symputx('dsoptions','keep=ACCOUNT NUMBER');
set ME.ALL_MEM_BKS;

keep account member;
where not missing(case_nr);
call symputx('dsoptions','where=(not missing(case_nr))');
stop;
run;

Re: Export based on condition of dataset

Tom — Fri, 29 Mar 2024 03:49:43 GMT

A KEEP statement there does nothing at all.

Since you are running a data _NULL_ step NONE of the variables are "kept" since there is no dataset at all that is going to be written.