topic Re: How to Group and add new columns in new table. in SAS Programming

How to Group and add new columns in new table.

deega — Fri, 17 Jun 2016 02:33:45 GMT

I have following table

Input
S.No.	Datetime	Details
1	2010/6/7 19:01	asd
1	2010/6/8 4:00	dfg
2	2010/6/9 0:00	dfg
2	2010/6/10 0:00	gfd
2	2010/6/11 0:00	gfd
3	2010/6/12 0:00	gfd
3	2010/6/13 0:00	abc
4	2010/6/14 0:00	abc
4	2010/6/15 0:00	def

and I am trying to get the following output

output	TimeDifference 1	TimeDifference 2	Detail 1	Detail 2	Detail 3
1	0.374305556		asd	dfg
2	1	1	dfg	gfd	gfd
3	1		gfd	abc
4	1		abc	def

I am using SAS Base 9.4. Till now I have tried Proc SQL, GroupBy and Proc Tabulate but its not working. Although I have not reached the stage to calculate time difference but I am thinking of using INTCK for the same.

If somebody can think of any approach, please share. waiting for somebody to rescue me from this situation.

Thanks in advance !

Re: How to Group and add new columns in new table.

PGStats — Fri, 17 Jun 2016 03:15:11 GMT

INTCK is not meant to return fractional time interval counts. You could do this:

data td;
input SNo dt & :anydtdtm.	Details $;
format dt datetime.;
datalines;
1	2010/6/7 19:01  asd
1	2010/6/8 4:00  dfg
2	2010/6/9 0:00  dfg
2	2010/6/10 0:00  gfd
2	2010/6/11 0:00  gfd
3	2010/6/12 0:00  gfd
3	2010/6/13 0:00  abc
4	2010/6/14 0:00  abc
4	2010/6/15 0:00  def
;

proc sql;
select max(n), max(n)-1 into :nDt trimmed, :nDt1 trimmed
from (select count(*) as n from td group by SNo);
quit;

data want;
array detail_{&nDt} $8;
array TimeDifference_{&nDt1};
do i = 1 by 1 until(last.SNo);
    set td; by SNo;
    detail_{i} = details;
    if i > 1 then timeDifference_{i-1} = (dt - dt1) / '24:00:00't;
    dt1 = dt;
    end;
drop i dt dt1 details;
run;

proc print; run;

Re: How to Group and add new columns in new table.

Ksharp — Fri, 17 Jun 2016 03:28:11 GMT

The simplest way is IDGROUP. If you have big table ,Try MERGE Skill proposed by me,Arthur.T,Matt :

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data have;
infile cards expandtabs truncover;
input No	Datetime & anydtdtm.	Details $;
format Datetime datetime20.;
cards;
1	2010/6/7 19:01	asd
1	2010/6/8 4:00	dfg
2	2010/6/9 0:00	dfg
2	2010/6/10 0:00	gfd
2	2010/6/11 0:00	gfd
3	2010/6/12 0:00	gfd
3	2010/6/13 0:00	abc
4	2010/6/14 0:00	abc
4	2010/6/15 0:00	def
;
run;

data have;
 merge have have(firstobs=2 keep=No Datetime 
 rename=(No=_No Datetime=_Datetime));
 if No=_No then dif=divide(_Datetime-Datetime,'24:00:00't);
 drop _:;
run;
proc sql noprint;
select max(n) into : n separated by ' '
 from (select count(*) as n from have group by No);
quit;
proc summary data=have;
by No;
output out=want(drop=_: dif_&n) idgroup(out[&n] (dif Details)=);
run;

Re: How to Group and add new columns in new table.

deega — Fri, 24 Jun 2016 00:37:28 GMT

Thanks for your prompt reply the other day. Now in the same problem I am facing a new issue. Actually I have big data in CSV format. When I import the data through SAS'S import wizard or proc import I get the dates in text format. Could you advise anything to get it in dates format ?

Re: How to Group and add new columns in new table.

Ksharp — Fri, 24 Jun 2016 00:56:48 GMT

Copy the code generated by PROC IMPORT from LOG and Paste it into programm editor, Change that date variable's informat and see what happen ?

Re: How to Group and add new columns in new table.

deega — Fri, 24 Jun 2016 02:20:07 GMT

Thank you so much for the suggestion but I deleted time from my datetime field in CSV file, after which I was able to import it as date. Since, now I dont have that old input so I can not try this option right now, may be some other time.

Re: How to Group and add new columns in new table.

Ksharp — Fri, 24 Jun 2016 02:56:16 GMT

Good news. Here are two choices:

1) Try some other option.

proc import .....;
scantime=yes;
usedate=yes;

2)Once import into SAS, use data step to transform it.

want_date=input(str_date,anydtdte.);

format want_date date9.;

Re: How to Group and add new columns in new table.

deega — Fri, 07 Oct 2016 03:00:47 GMT

@PG Stats@PG Stats
Thanks for this program.
Could you please tell the use/reason of using {&nDt} while defining arrays. In the basics of arrays all I understood is that we need to give the number of elements...

Re: How to Group and add new columns in new table.

PGStats — Fri, 07 Oct 2016 03:23:11 GMT

The SQL step defines two macro variables nDt and nDt1 that hold the maximum number of observations in a SNo group and that same number minus one, respectively.

These macro values are used in the data step as array dimensions where &nDt and &nDt1 are mentioned.

Re: How to Group and add new columns in new table.

deega — Fri, 07 Oct 2016 04:56:03 GMT

@PG Stats@PG Stats
Thanks for the prompt reply.
I have written a program based on your program where I am not calculating Time Difference also. My Data set is around 15000000 records and program is running from last 2 hours, normally does it take this much time ?

Re: How to Group and add new columns in new table.

PGStats — Fri, 07 Oct 2016 17:01:15 GMT

That's not what I would expect if the program and the data are on the same machine. But then, I haven't seen your code.