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
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;
Thank you for the quick response Lakshmi. Is there a way the same result cane be achieved through SQL ?
Thanks.
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 🙂
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.
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;
Thanks Kiran. This is really helpful. !
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.