DATA Step, Macro, Functions and more

How to Group and add new columns in new table.

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

How to Group and add new columns in new table.

I have following table

Input  
S.No.DatetimeDetails
12010/6/7 19:01asd
12010/6/8 4:00dfg
22010/6/9 0:00dfg
22010/6/10 0:00gfd
22010/6/11 0:00gfd
32010/6/12 0:00gfd
32010/6/13 0:00abc
42010/6/14 0:00abc
42010/6/15 0:00def

 

and I am trying to get the following output

outputTimeDifference 1TimeDifference 2Detail 1Detail 2Detail 3
10.374305556 asddfg 
211dfggfdgfd
31 gfdabc 
41 abcdef 

 

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 !


Accepted Solutions
Solution
‎06-22-2016 09:53 AM
Super User
Posts: 9,691

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

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;

View solution in original post


All Replies
Respected Advisor
Posts: 4,659

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

[ Edited ]

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; 
PG
Contributor
Posts: 71

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

@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...
Respected Advisor
Posts: 4,659

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

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.

PG
Contributor
Posts: 71

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

@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 ?
Respected Advisor
Posts: 4,659

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

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.

PG
Solution
‎06-22-2016 09:53 AM
Super User
Posts: 9,691

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

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;
Contributor
Posts: 71

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

[ Edited ]

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 ?

Super User
Posts: 9,691

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

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 ?

Contributor
Posts: 71

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

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. 

Super User
Posts: 9,691

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

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.;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 398 views
  • 3 likes
  • 3 in conversation