BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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.

Ronein
Meteorite | Level 14

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

 

 

Kurt_Bremser
Super User

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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