Macro help for data step automation

[saslove]

Can someone please help me create an automation/macro for this data step. Right now, I get data on a daily basis and I have to create fileid every day which is a pain when I get more than 1000 file ids. Is there a way it can automatically pick the total file ids from my stats data to create the %fileid(num=n) datasets, inN datasets and newN datasets. And is there a way to reduce the macro to few lines without filling the editor window. Please help.

For more information, this is what I am trying to achieve

https://communities.sas.com/message/185370#185370

Many thanks in advance!

/*Comparing for unique VehicleIds*/ /*Update everyday*/

%macro fileid (num=);

proc sort data=tripstats;

by fileid vehicleid;

data fileid#

set stats;

by fileid vehicleid;

if fileid = #

if first.vehicleid then output;

run;

proc sort data=fileid#

by vehicleid;

run;

%mend fileid;

%fileid (num=6);%fileid (num=7);%fileid (num=8);%fileid (num=9);%fileid (num=10);%fileid (num=11);%fileid (num=12);%fileid (num=13);

%fileid (num=14);%fileid (num=15);%fileid (num=16);%fileid (num=17);%fileid (num=18);%fileid (num=19);%fileid (num=20);

proc sort data=stats;

by fileid vehicleid;

data fileid5;

set stats;

by fileid vehicleid;

if fileid=5;

if last.vehicleid then output;

run;

proc sort data=fileid5;

by vehicleid;

run;

/*New vehicles in each fileid*/

title1 "Unique vehicles in each fileid";

data want ;

  merge fileid5 (in=in5 keep=vehicleid)

        fileid6 (in=in6 keep=vehicleid)

        fileid7 (in=in7 keep=vehicleid)

        fileid8 (in=in8 keep=vehicleid)

        fileid9 (in=in9 keep=vehicleid)

        fileid10(in=in10 keep=vehicleid)

  fileid11(in=in11 keep=vehicleid)

  fileid12(in=in12 keep=vehicleid)

  fileid13(in=in13 keep=vehicleid)

  fileid14(in=in14 keep=vehicleid)

  fileid15(in=in15 keep=vehicleid)

  fileid16(in=in16 keep=vehicleid)

  fileid17(in=in17 keep=vehicleid)

  fileid18(in=in18 keep=vehicleid)

  fileid19(in=in19 keep=vehicleid)

  fileid20(in=in20 keep=vehicleid)

...................................

    end=eof

  ;

  by vehicleid;

  if first.vehicleid;

  new6 + (in6 and ^in5);

  new7 + (in7 and ^in6 and ^in5);

  new8 + (in8 and ^in7 and ^in6 and ^in5);

  new9 + (in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new10+ (in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new11+ (in11 and ^in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new12+ (in12 and ^in11 and ^in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new13+ (in13 and ^in12 and ^in11 and ^in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new14+ (in14 and ^in13 and ^in12 and ^in11 and ^in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new15+ (in15 and ^in14 and ^in13 and ^in12 and ^in11 and ^in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new16+ (in16 and ^in15 and ^in14 and ^in13 and ^in12 and ^in11 and ^in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new17+ (in17 and ^in16 and ^in15 and ^in14 and ^in13 and ^in12 and ^in11 and ^in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new18+ (in18 and ^in17 and ^in16 and ^in15 and ^in14 and ^in13 and ^in12 and ^in11 and ^in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new19+ (in19 and ^in18 and ^in17 and ^in16 and ^in15 and ^in14 and ^in13 and ^in12 and ^in11 and ^in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  new20+ (in20 and ^in19 and ^in18 and ^in17 and ^in16 and ^in15 and ^in14 and ^in13 and ^in12 and ^in11 and ^in10 and ^in9 and ^in8 and ^in7 and ^in6 and ^in5);

  .....................

if eof;

  put (new6-new108) (=/);

run;

[Peter_C]

Have a look at the UPDATE statement.

xxxxxxxxxxxx

Message was edited by: Peter Crawford Strike that idea.  Only vehicleID and a descriptive var are needed

[Peter_C]

It might be more easily solved with interleaved SET using INDSNAME= to identify from which table a VehicleID first appears.

Then it becomes important to order the tables appearing on the SET into proper chronology.

A further enhancement might retain the table of vehicleID and first-appearance-ref. Then you only need insert VehicleID (with a new ref) which are NOT already in the table - probably SQL could do the whole thing in a couple of queries.

[Peter_C]

here is some code to create a register of VEHICLEID along with the smallest ref number in which the vehicleID is found.

(a natural extra I presumed to add was the date on which the addition to the register occurs)

data LATEST_id( label= 'see https://communities.sas.com/message/187144#187144 '

  index=( vehicleID/unique refn) )  ;

length  vehicleID $12  LATEST REFn 8 ;

ATTRIB LATEST FORMAT= DATE9. INFORMAT= DATE. ;

STOP ;

CALL MISSING( OF _ALL_ ) ;

RUN ;

and here is a macro that uses SQL to add new vehicleID values to the register

%macro pasttimes( fromf=, tof= ) ;

%local f ;

%do f= &fromf %to &tof ;

data fileid&F ; * simulate data collection ;

length  vehicleID $12 ;

retain a1-a100 8 ;

do _n_ = 1 to 1000 ;

  vehicleID = put( _n_, z12. ) ;

  _n_ + int( ranuni( &f) *100 ) ;

output ;

end ;

stop ;

run ;

*now insert only NEW vehicleID into register ; ;

PROC SQL   undo_policy= none ;

INSERT INTO LATEST_id

SELECT distinct vehicleID, today(), &f

from fileid&f where vehicleID not in( select vehicleID from latest_ID );

%put &f -----> &sqlobs ; *show number of inserts ;

quit ;

%end ;

%mend  pasttimes ;

*now run 200 "days" ;

option nomlogic nosymbolgen nomprint ;

%pasttimes( fromf=1, tof= 200) ;

*Now the reporting phase to get the number of new vehicleID by refN number;

proc summary data= latest_ID ;

class refN ;

output out= refN_freq ;

run ;