BookmarkSubscribeRSS Feed
saslove
Quartz | Level 8

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;

3 REPLIES 3
Peter_C
Rhodochrosite | Level 12

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
Rhodochrosite | Level 12

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
Rhodochrosite | Level 12

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 ;


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
  • 915 views
  • 0 likes
  • 2 in conversation