DATA Step, Macro, Functions and more

PROC SQL and Window Functions

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

PROC SQL and Window Functions

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


Accepted Solutions
Solution
‎04-13-2017 06:04 PM
PROC Star
Posts: 325

Re: PROC SQL and Window Functions

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;

 

View solution in original post


All Replies
Contributor
Posts: 57

Re: PROC SQL and Window Functions

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

Re: PROC SQL and Window Functions

Posted in reply to lakshmi_74

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

 

Thanks.

Contributor
Posts: 57

Re: PROC SQL and Window Functions

Posted in reply to 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;
Frequent Contributor
Posts: 75

Re: PROC SQL and Window Functions

Posted in reply to lakshmi_74
Thank you Lakshmi. I have to implement the solution in Teradata, so any suggestion on how to implement this in SQL Query ?
Super User
Posts: 19,789

Re: PROC SQL and Window Functions

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 Smiley Happy

 

Frequent Contributor
Posts: 75

Re: PROC SQL and Window Functions

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

Re: PROC SQL and Window Functions

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.
Super User
Posts: 19,789

Re: PROC SQL and Window Functions

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.  

 

Solution
‎04-13-2017 06:04 PM
PROC Star
Posts: 325

Re: PROC SQL and Window Functions

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;

 

Frequent Contributor
Posts: 75

Re: PROC SQL and Window Functions

 

Thanks Kiran. This is really helpful. ! 

☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 438 views
  • 1 like
  • 4 in conversation