How do I: Loop through the data to build a timestamp string.

[Halaku]

In DIS I need to loop through records and build a string with date and time from a table that stores date and time in two separate rows. Query on a unique CID will always fetch 3 rows. Here is the sample data:

CID	PGID	ELID	EV
212673	509279	1	Turn On  or Off Water Service Line
212673	509281	1	13 Oct  2011
212673	509281	2	10

What I like to do is to ignore the first row and walk through the other two rows and grab the EV values (13 Oct  2011) and (10) to build an entire timestamp string like this:

13-OCT-2011 10:00:00.

Time is always stored in hours.

Any hints and help are much appreciated.

[art297]

Halaku,  Unfortunately I'm not familiar with either EG or DIS but, if you can do it in a datastep, you could use something like:

data have;

  informat EV $50.;

  format thedate date9.;

  format datetime datetime21.;

  retain last_ev;

  input CID PGID ELID EV &;

  if input(ev, ?? anydtdte21.) gt 0 then do;

    thedate=input(ev, anydtdte21.);

    last_ev=thedate;

  end;

  else do;

    if last_ev and input(ev,?? 2.) gt 0 then do;

      datetime=input(catt(put(last_ev,date9.),":",input(ev,2.),

       ":00:00"),anydtdtm21.);

    end;

    call missing(last_ev);

  end;

  cards;

212673 509279 1 Turn On or Off Water Service Line

212673 509281 1 13 Oct 2011

212673 509281 2 10

;

run;

[LinusH]

Well, there is no transform available in dis that will do this for you "out of the box".

Probably the easiest is to use code such as Art's in a user written transform.

Given the prerequisite that the table is sorted by CID & PGID, then you could do the following:

- use extract with group by and a having that filters out min(PGID).

- transpose transform to get date and hour on the same row.

- any transform that let's you define expressions (extract, sql join or table loader).

Linus

[Tom · Accepted solution]

I am not sure that I understand the structure that you are trying to read.  But if the date is always on the row where pgid=509281 and elid=1 and the hour is always on the row where pgid=509281 and elid=2 then you can just merge those rows back together so that you can build a datetime value.

data have;
  input cid pgid elid ev $50.;
cards;
212673 509279 1 Turn On or Off Water Service Line
212673 509281 1 13 Oct 2011
212673 509281 2 10
run;
data want ;
  merge have(where=(pgid=509281 and elid=1) rename=ev=cdate)
        have(where=(pgid=509281 and elid=2) rename=ev=chour)
  ;
  by cid;
  timestamp = input(compress(cdate||':'||chour||':00:00'),anydtdtm.);
  format timestamp datetime.;
  put cid= timestamp=;
run;

[TimStearn_SASProductManagement_]

Halaku,

From what data source are you querying?  If you're querying a relational database like MySQL, Oracle, etc, some of these databases have specialized functions that allow you to concatenate strings across rows.  If your source is such a database, you could accomplish this by turning on passthrough in the SQL Query transformation.  If this is not the case, I agree with the previous posts that the solution would be to use the User Written Code node and write a SAS program that takes advantage of retain variables.

[Halaku]

Thanks to all who replied. I am extracting the data from Oracle and need to post is to SQL server. Transpose seems to be a good choice as I am not very much into SAS programming yet. Will appreciate if some one can get a simple approach to this problem.

And if I am writing my own Transformation how do I connect to the output from previous transformation result ( say EXTRACT) and to output to other transformation.

[Tom]

The easiest thing is to just create libnames that point to your external databases. Then you can access your database tables as if they were SAS datasets.

libname oracle oracle user=testuser password=testpass path=hrdept_002;

libname sqlsvr sqlsvr user=testuser password=testpass;

data sqlsrv.want ;

  merge oracle.have(where=(pgid=509281 and elid=1) rename=ev=cdate)

        oracle.have(where=(pgid=509281 and elid=2) rename=ev=chour)

  ;

  by cid;

  timestamp = input(compress(cdate||':'||chour||':00:00'),anydtdtm.);

  format timestamp datetime.;

run;

[Halaku]

I created a Generated Transformation (GT) with the following code and it is runing successfully as it returns values through my put ... statement at the end. However, despite I have checked the boxes for Input/Output tab for both input and output macro variables, I am unable to pass the values for cid and tstamp to my next transformation which is a dataloader. I'll appreciate help in mapping the values to output work table. If you need details I can do and export of the job and submit screenshots as well.

Thanks,

-------------------------------------------------------------------------------------------

data want;

set &SYSLAST;

  merge &syslast(where=(element_id=1) rename=value=cdate)

        &syslast(where=(element_id=2) rename=value=chour)

  ;

  by case_id;

  timestamp = input(compress(cdate||':'||chour||':00:00'),anydtdtm.);

  format timestamp datetime.;

  /*put timestamp;

  */

   vcid=case_id;

   vtstamp=timestamp;

   put vcid vtstamp;

run;

----------------------------------------------------------------------------------------------------------

[Tom]

I do not know anything about SAS Enterprise Data Management & Integration, but if you want a data step to generate a macro variable use CALL SYMPUTX in place of your PUT statement.

call symputx('vcid',case_id);

call symputx('vtstamp',timestamp);

Note that this will only work correctly when there is only one observation.

If there are mulitple observations then only the last value will remain in the macro variable as the previous values will have been overwritten.