DATA Step, Macro, Functions and more

Conditionally Import

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 97
Accepted Solution

Conditionally Import

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;


Accepted Solutions
Solution
‎04-04-2018 04:33 AM
Super User
Posts: 9,855

Re: Conditionally Import

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).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 9,855

Re: Conditionally Import

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 97

Re: Conditionally Import

Posted in reply to KurtBremser

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

 

 

Solution
‎04-04-2018 04:33 AM
Super User
Posts: 9,855

Re: Conditionally Import

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).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 114 views
  • 0 likes
  • 2 in conversation