BookmarkSubscribeRSS Feed
twildone
Pyrite | Level 9

Hi, I am currently using the code below to select records from a dataset labelled 'Plan' and from this new dataset ( 'mydata'), it creates or adds new records by changing the "OldID" to the "NewIDs". It works fine. The problem is that I have to constantly change or modify the code if the number of NewIDs change. Any suggestions would be greatly appreciated. Thanks in Advance.

%LET Eff_Date1=20141016;
%LET Exp_Date1=99999999;
%LET load_date=20141014;
%LET OldID=02247029;
%LET NewIDs1=02414805;
%LET NewIDs2=02414791;
%LET NewIDs3=02414783;
%LET fil1=Keppral;

%MACRO INSERTS(Eff_Date,Exp_Date,ID,NewID1,NewID2,NewID3,fil);
PROC SQL NOPRINT;
  CREATE TABLE mydata as
SELECT
  PLAN.*
FROM WORK.PLAN
  WHERE  ( PLAN.ID = "&ID" )
     AND
         (( PLAN.EF_EFF_DATE <= &Exp_Date )
         AND 
   ( PLAN.EP_EXP_DATE >= &Eff_Date ))
;

PROC SORT DATA=mydata;
BY _ALL_;
RUN;

DATA &fil;
  SET want;
  Ex_Eff_Date=&Eff_Date;
  %DO I = 1 %TO 3;
      ID="&&NewID&I";
      OUTPUT;
      %END;
RUN;

%MEND INSERTS;

%INSERTS(&Eff_Date1,&Exp_Date1,&OldID,&NewIDs1,&NewIDs2,&NewIDs3,&fil1);

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

I would suggest any kind of unknown number of parameters such as this would be better suited to storing in a dataset.  Then when new ones are added the code does not change:

/* Some test data */
data ids;
     newid="02414805"; output;
     newid="02414791"; output;
     newid="02414783"; output;
run;

/* Test macro */
%macro Hi (the_id=);
     %put &the_id.;
%mend Hi;

/* Actually using the data */
data _null_;
  set ids;
  call execute('%Hi (the_id="'||strip(newid)||'");');
run;
    

Patrick
Opal | Level 21

You write your macro is doing what you want. I don't understand. I don't see how the code you've posted is actually doing what you describe.

The SQL creates "mydata" which you then also sort by "_all_" - but no "nodupkey" or anything so this sort is kind-of mysterious. But then "mydata" is no more used at all (unlike to your narrative).

The "data &fil" step uses a data set "want" as source - but "want" doesn't get created anywhere.

I suggest you post some representative sample data (a data step creating such data), describe what you want to achieve and best also post the desired result based on your sample data.

Astounding
PROC Star

The key ingredient is going to be to expand your macro language skills.  You will end up with a single macro variable holding all the IDs:

%let ID_List = 02414805 02414791 02414783;

There's more than one way to accomplish this.  As one posted mentioned, if you have a large number of IDs you might want to store them in a SAS data set and use your expanded macro language skills to get the entire list into a single macro variable.

Then you will need to learn how to capture the number of elements in the list as a macro variable, so the %do loop does not have to be hard-coded:

%DO I=1 %to &n_elements;

Then you will learn to use the %SCAN function to pick out an individual ID.  When &I = 1, retrieve the first ID in the list.  When &I = 2, retrieve the second ID in the list.

None of this is rocket science, but all of it represents learning new skills.

Good luck.

data_null__
Jade | Level 19

Astounding wrote:

The key ingredient is going to be to expand your macro language skills.  You will end up with a single macro variable holding all the IDs:

%let ID_List = 02414805 02414791 02414783;

Then you will need to learn how to capture the number of elements in the list as a macro variable, so the %do loop does not have to be hard-coded:

%DO I=1 %to &n_elements;

Then you will learn to use the %SCAN function to pick out an individual ID.  When &I = 1, retrieve the first ID in the list.  When &I = 2, retrieve the second ID in the list.

If the entire list of "words" is to be processed from ID_LIST there is no need to count the number of "words" for your &N_ELEMENTS.  You can more easily just scan until there are no more words.

Astounding
PROC Star

data _null_,

Technically, what you say is 100% true.  As a matter of giving advice, however, I would disagree.  For someone who is in "expanding skills" mode, %DO %WHILE and %DO %UNTIL are harder to learn (just my opinion, of course).  So that was part of the thinking behind my response.  The other factor I considered was that counting the number of elements in a list will be vital at some point, even if not for this application.  Better to learn it now when there is a real life problem at hand that could be used for practice.

ballardw
Super User

And where do you get the values for the NewID variables? If from code somewhere then storing them per RW9 might be helpful.

twildone
Pyrite | Level 9

Hi, I made some changes as suggested and have attached the code below. It seems to work except it doesn't populate the corresponding Din numbers in the Din Field and excludes to original records with the OldDin in the Din Field. Otherwise, it seems to loop through and create the number of new records that I would like to be added to the previous record. Thanks.

%MACRO INSERTS(EFF_DATE,EXP_DATE,PRODUCT,FIL);
PROC SQL NOPRINT;
  CREATE TABLE mydata AS
SELECT 
    PLAN.*
FROM WORK.PLAN
  WHERE
       ( PLAN.INC_FLAG = "Y" )     /* PRODUCT to be included */
     AND
       ( PLAN.PRODUCT = "&PRODUCT" )           /* PRODUCT */
    AND
  (PLAN.CID IN (1,3))
  AND
  ( PLAN.EX_EXP_DATE ^= 99999999)
  AND
       (( PLAN.EX_EFF_DATE <= &EXP_DATE )
        AND 
  ( PLAN.EX_EXP_DATE >= &EFF_DATE ))
;

PROC SORT DATA=mydata;
BY CID PRODUCT ID EX_EFF_DATE EX_EXP_DATE APPLICATION_DATE;
RUN;

DATA WANT;
SET mydata;
  BY CID PRODUCT ID EX_EFF_DATE EX_EXP_DATE APPLICATION_DATE;
   IF LAST.ID;
RUN;
                                                                                                                                                                                                      
PROC SORT DATA=mydata;
BY CID PRODUCT ID;
RUN;

PROC SQL NOPRINT;
SELECT COUNT(PRODUCT) INTO :COUNTS SEPARATED BY ' '
FROM WORK.DINS;
QUIT;
RUN;
%PUT &COUNTS;

PROC SQL NOPRINT;
SELECT PRODUCT INTO :OBSERVATIONS SEPARATED BY ' '
FROM WORK.DINS;
QUIT;
RUN;
%PUT &OBSERVATIONS;

DATA &FIL;
  SET want;
  EX_EFF_DATE=&EFF_DATE;
  %DO I = 1 %TO &COUNTS;
    %PUT PRODUCT=%SCAN(&OBSERVATIONS,&I);
  PRODUCT = "&&PRODUCT&I";
      OUTPUT;
      %END;
RUN;

PROC SORT DATA=&FIL NODUPKEY;
  BY CID ID PRODUCT EX_EFF_DATE APPLICATION_DATE;
RUN;

PROC SQL;
    CREATE TABLE Final AS
     SELECT &FIL..CID,
          &FIL..PRODUCT,
          &FIL..ID,
          &FIL..EX_EFF_DATE,
    &FIL..EX_EXP_DATE,
            (&LOAD_DATE) AS LOAD_DATE,
          &FIL..APPLICATION_DATE,
       FROM WORK.&FIL;
QUIT;
RUN;

PROC SORT DATA=FINAL;
BY CID PRODUCT ID;
RUN;

%MEND INSERTS;

%INSERTS(&EFF_DATE1, &EXP_DATE1, &OLDDIN,&FIL1);

ballardw
Super User

It is hard to tell what might be happening with your DIN field as you don't say which variable it might be or where it originates.

twildone
Pyrite | Level 9

Hi Ballardw.....this is where the DINs originate from:

DATA DINS;
  LENGTH DIN $ 8;
  INPUT DIN $;
  DATALINES;
02418932
02418940
02420155
02420163
;

PROC SORT DATA=dins NODUPKEY;
  BY DIN;
RUN;

The DIN field populates with $PRODUCT1, &PRODUCT2, &PRODUCT3 and &PRODUCT4.


Astounding
PROC Star

Well done, taking it this far.  Just in case there are still any issues, here a few comments.

One of the PROC SORTs can come out:

PROC SORT DATA=mydata;

BY CID PRODUCT ID;

RUN;

The data had already been sorted, and they remain in order.  So the second PROC SORT isn't needed (check the log ... SAS probably figured out it should skip this second sort).

If there is still an issue with the PRODUCT values, the suspect is here:

PRODUCT = "&&PRODUCT&I";

That would be the correct syntax if you had already split &OBSERVATIONS into separate macro variables named &PRODUCT1, &PRODUCT2, ... &PRODUCTn.  Here, all you really need is the same syntax that worked in the

%PUT statement:

PRODUCT = "%SCAN(&OBSERVATIONS,&I)";

You may have already tackled that, so this is in the spirit of "just in case it helps".

twildone
Pyrite | Level 9

Hi...Thank You to everyone for their comments and suggestions. With your help, I was able to solve the problem.

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
  • 11 replies
  • 1299 views
  • 6 likes
  • 6 in conversation