topic Re: PROC SQL and Window Functions in SAS Programming

PROC SQL and Window Functions

dhana — Thu, 06 Apr 2017 01:40:21 GMT

Hi,

I have a data like this and I am trying to create a new column based on GROUP and TAG. If TAG is populated repeat the same TAG in the subsequent rows until it finds a new TAG or go to a new GROUP.

GROUP	SEQ	TAG
1	1
1	2
1	3
1	4	4
1	5
1	6
1	7
1	8
2	1
2	2
2	3	3
2	4
2	5
2	6
2	7	7
2	8
2	9

The resultant table will look like this

GROUP	SEQ	TAG	NEWTAG
1	1
1	2
1	3
1	4	4	4
1	5		4
1	6		4
1	7		4
1	8		4
2	1
2	2
2	3	3	3
2	4		3
2	5		3
2	6		3
2	7	7	7
2	8		7
2	9		7

Is there a way I can utilize windowing function LAG. ?

Thanks

Re: PROC SQL and Window Functions

lakshmi_74 — Thu, 06 Apr 2017 01:57:36 GMT

data want;
set have;
retain new_tag ;
if missing(tag) and _n_ gt 1 then
do;
if not missing(lag(tag)) then new_tag=lag(tag);
end;
else new_tag=tag;
final_tag=new_tag;run;

Re: PROC SQL and Window Functions

dhana — Thu, 06 Apr 2017 02:07:01 GMT

Thank you for the quick response Lakshmi. Is there a way the same result cane be achieved through SQL ?

Thanks.

Re: PROC SQL and Window Functions

lakshmi_74 — Thu, 06 Apr 2017 02:11:01 GMT

According to your requirement you don't require lag function, the following code is perfect for you.
data want;
set have;
by group;
retain new_tag ;
if missing(tag) and first.group then new_tag=tag;
else if not missing(tag) then new_tag=tag;
final_tag=new_tag;
run;

Re: PROC SQL and Window Functions

dhana — Thu, 06 Apr 2017 02:21:35 GMT

Thank you Lakshmi. I have to implement the solution in Teradata, so any suggestion on how to implement this in SQL Query ?

Re: PROC SQL and Window Functions

Reeza — Thu, 06 Apr 2017 02:35:39 GMT

So if you're doing this in Teradata are you using their flavour of SQL or are you using SAS? Are you using SQL pass through? Where does SAS come into this process and where does Teradata?

If it's Teradata SQL you should probably ask your question on a Teradata forum, not a SAS forum 🙂

Re: PROC SQL and Window Functions

dhana — Thu, 06 Apr 2017 03:29:00 GMT

I am using SAS to connect and extract data from Teradata. As part of my extraction, I want to implement the above logic.

Re: PROC SQL and Window Functions

lakshmi_74 — Thu, 06 Apr 2017 03:35:54 GMT

You can connect data into sas dataset and can use above logic.
As in proc sql you can use lag() only to bring the previous value and then you have to use join function. But your request is quite difficult to implement by using proc sql.

Re: PROC SQL and Window Functions

Reeza — Thu, 06 Apr 2017 03:37:46 GMT

SAS SQL doesn't support windowing functions.

You can do a self join on the table to itself where ids are the same and seq is larger than the sequence to carry down the TAG values without using windowing assuming the SEQ is as indicated in your sample.

Re: PROC SQL and Window Functions

kiranv_ — Tue, 11 Apr 2017 19:57:52 GMT

I saw similar answer around 6 months interadata forum but was not able to trace the answer.

Below logic should work in sql pass through

proc sql;

connect to teradata (server=myserver user=myuserid pw=mypass);

execute(create table teraschema.table1 as

(

select "group",

"seq",

taq,

nullifzero(case when taq = 0 then

taq + max(tag) over(partition by grou order by seq1 rows between unbounded preceding and current row )

else tag

end) as newtag

from(

select "group", "seq", case when tag is null then 0 else tag end as tag from abc)x

) with data prrimary index(anyuniquecolumn))by teradata;

execute(commit) by teradata;

disconnect from teradata;

quit;

Re: PROC SQL and Window Functions

dhana — Thu, 13 Apr 2017 22:04:33 GMT

Thanks Kiran. This is really helpful. !