DATA Step, Macro, Functions and more

RE:

Reply
Regular Contributor
Posts: 222

RE:

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

Super User
Super User
Posts: 7,401

Re: RE:

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;
    

Respected Advisor
Posts: 3,890

Re: RE:

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.

Super User
Posts: 5,082

Re: RE:

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.

Respected Advisor
Posts: 3,777

Re: RE:

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.

Super User
Posts: 5,082

Re: RE:

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.

Super User
Posts: 10,500

Re: RE:

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

Regular Contributor
Posts: 222

Re: RE:

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 Smiley SurprisedBSERVATIONS 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);

Super User
Posts: 10,500

Re: RE:

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.

Regular Contributor
Posts: 222

Re: RE:

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.


Super User
Posts: 5,082

Re: RE:

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

Regular Contributor
Posts: 222

Re: RE:

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

Ask a Question
Discussion stats
  • 11 replies
  • 427 views
  • 6 likes
  • 6 in conversation