BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dhana
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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

10 REPLIES 10
lakshmi_74
Quartz | Level 8
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
Fluorite | Level 6

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

 

Thanks.

lakshmi_74
Quartz | Level 8
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
Fluorite | Level 6
Thank you Lakshmi. I have to implement the solution in Teradata, so any suggestion on how to implement this in SQL Query ?
Reeza
Super User

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
Fluorite | Level 6
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
Quartz | Level 8
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
Super User

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_
Rhodochrosite | Level 12

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
Fluorite | Level 6

 

Thanks Kiran. This is really helpful. ! 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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