topic Re: Conditionally Import in SAS Programming

Conditionally Import

Ronein — Wed, 04 Apr 2018 07:00:00 GMT

Hello

I have the following issue.

I run a monthly report based on a monthly input file.

IF there is a problem with field Y input file then there is an extra table that we use in order to fix the input file.

How can I tell SAS that IF field Y is Null in all rows then need to import an extra file and then merge input file with repair file in order to fix the field.

For example:

Data Input_tbl;
input ID X Y;
cards;
1 10 .
2 20 .
3 30 .
;
run;

Data Reapir_tbl;
input ID Y;
cards;
1 100
2 200
3 300
;
run;

The import of the repair file is done by proc import

proc import datafile="D:/Reapir_tbl.csv"
out=Reapir_tbl
dbms=csv
replace;
getnames=yes;
run;

Re: Conditionally Import

Kurt_Bremser — Wed, 04 Apr 2018 07:09:00 GMT

First, if you have a repeating process,

DO NOT USE PROC IMPORT!

Use proc import once to get a data step, and use that in the future. You will catch structural differences because your data step will fail when the structure changes.

If you simply need to replace "valid" missing values, do this:

Data  Input_tbl;
input ID X Y;
cards;
1 10 .
2 20 .
3 30 .
;
run;

Data Repair_tbl;
input ID Y;
cards;
1 100
2 200
3 300
;
run;

%macro check_and_repair;
proc sql noprint;
select max(y) into :y from input_tbl;
quit;

%if &y = . %then %do;

data input_tbl;
merge
  input_tbl
  repair_tbl
;
by id;
run;

%end;
%mend;

%check_and_repair

Note that I use the proper subwindows for posting code, see https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce

The main posting window will trash your indentation and do other funny things with code.

Re: Conditionally Import

Ronein — Wed, 04 Apr 2018 08:01:48 GMT

Perfect and thank you so much.

Just one issue need to add to your code.

When max(y) is null then we need to import the repair file because this file is not in SAS.

As you see it is Conditionally Import because it is done only if max(y) is null.

Can you please add it to the code you sent?

thanks

Re: Conditionally Import

Kurt_Bremser — Wed, 04 Apr 2018 08:25:40 GMT

Move the data step from the log of the proc import into the %then-%do-%end block in the macro. Just a copy-paste (remove the line numbers by holding down the alt key while selecting text).