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

Hi. I have the following macro that iterates multiple times identifying various records. Records are unique by IMB_CODE. With each iteration the identified data is appended into my FinalData dataset.

 

My problem is that the query can identify the same record (with the same IMB_CODE) in later iterations. When this happens I must have a way to only keep the first instance of the record in my FinalData dataset.. 

 

Any suggestions?

 

 

%macro BuildQueryData(analysis_desc= , rule= , rule_order= );

 

proc sql;
connect to oracle as db (user="me" password="mepw" path="medb");
create table MyRuleQuery as
select * from connection to db

 

( select '&analysis_desc' as RULE_NM, b.actual_dlvry_date as AD_DT, b.imb_code, &rule_order as rule_order
from ivprl.bi_spm_piece_iv_recon a, ivprl.bi_spm_piece_bids_recon b
where trunc(a.upload_create_dt) = trunc(sysdate)
and trunc(b.upload_create_dt) = trunc(sysdate)
and a.imb_code = b.imb_code
and a.imb_dlvry_zip_3 in(select imb_dlvry_zip_3 from ivprl.bi_spm_piece_bids_recon)
and a.ml_cl_code in(select ml_cl_code from ivprl.bi_spm_piece_bids_recon)
and a.ml_cat_code in(select ml_cat_code from ivprl.bi_spm_piece_bids_recon)
and a.imb_code not in(select imb_code from MyRuleQueryData where rule_order < 999.1)
and &rule);

 

disconnect from db;
quit;

 

proc append base=FinalData data = MyRuleQuery;
run;

%mend BuildQueryData;

 

 

data _null_;
set QueryRules;
call execute('%BuildQueryData(analysis_desc='||analysis_desc||' , rule='||rule||' , rule_order='||rule_order||');');
run;

1 ACCEPTED SOLUTION

Accepted Solutions
jklaverstijn
Rhodochrosite | Level 12

It is quite customary to preprocess the data you want to append by filtering out the rows with a key that's already in the base dataset. A simple SQL query should do (untested):

 

proc sql;
  create table newrows as
  select * form MyRuleQuery where ID
     not in (select distinct IMB_CODE from FinalData);
quit;

proc append base=FinalData data = newrows;
run;

 

Alternatively you van ceate a unique index on the target table.

 

PROC DATASETS LIBRARY=yourlib; 
MODIFY Finaldata; 
INDEX CREATE IMB_CODE / UNIQUE NOMISS ;
quit;

 

This will reject rows that have a key that is already present. Personally I dislike the (ab)use of a unique index (or actually the associated integrity constraint) as an ETL tool, but is very effective. Apply the unique index just the same as a safety net.

 

Best of luck,

- Jan.

Hope this helps,

- Jan.

View solution in original post

3 REPLIES 3
LinusH
Tourmaline | Level 20

proc sort nodupkey

Data never sleeps
buechler66
Barite | Level 11

But I need to guarentee the first instance inserted into the FinalData dataset is the one that stays.  I'm not sure NoDupKey guarentees the first record in is not eliminated.

jklaverstijn
Rhodochrosite | Level 12

It is quite customary to preprocess the data you want to append by filtering out the rows with a key that's already in the base dataset. A simple SQL query should do (untested):

 

proc sql;
  create table newrows as
  select * form MyRuleQuery where ID
     not in (select distinct IMB_CODE from FinalData);
quit;

proc append base=FinalData data = newrows;
run;

 

Alternatively you van ceate a unique index on the target table.

 

PROC DATASETS LIBRARY=yourlib; 
MODIFY Finaldata; 
INDEX CREATE IMB_CODE / UNIQUE NOMISS ;
quit;

 

This will reject rows that have a key that is already present. Personally I dislike the (ab)use of a unique index (or actually the associated integrity constraint) as an ETL tool, but is very effective. Apply the unique index just the same as a safety net.

 

Best of luck,

- Jan.

Hope this helps,

- Jan.

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
  • 2899 views
  • 1 like
  • 3 in conversation