PROC SQL and Window Functions

[dhana]

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

[lakshmi_74]

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;

[dhana]

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

Thanks.

[lakshmi_74]

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;

[dhana]

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

[Reeza]

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 🙂

[dhana]

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

[lakshmi_74]

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.

[Reeza]

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.  

[kiranv_ · Accepted solution]

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;

[dhana]

Thanks Kiran. This is really helpful. !