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;
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).
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.
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
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).
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.