topic Macro help for data step automation in SAS Programming

Macro help for data step automation

saslove — Tue, 05 Nov 2013 21:30:39 GMT

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&num;

set stats;

by fileid vehicleid;

if fileid = &num;

if first.vehicleid then output;

run;

proc sort data=fileid&num;

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;

Re: Macro help for data step automation

Peter_C — Wed, 06 Nov 2013 22:47:11 GMT

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

Re: Macro help for data step automation

Peter_C — Thu, 07 Nov 2013 15:32:35 GMT

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.

Re: Macro help for data step automation

Peter_C — Sun, 10 Nov 2013 17:31:16 GMT

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 ;