topic Re: Proc SQL equivalent for If first. then do if last. in SAS Programming

Proc SQL equivalent for If first. then do if last.

wbsjd — Mon, 06 Jun 2022 23:23:39 GMT

Hello Everyone:

Is it possible to write the following SAS data step in Proc SQL? I have been struggled for a long time. Thank you so much in advance.

data hsshow(/*drop=days_span*/);
    set show_all; 
    by member_i prognum mon;    
    if first.mon then days_elig=0;
	days_elig + days_span;
    if days_elig gt days_in_mon then days_elig=days_in_mon;
	days_gap = days_in_mon - days_elig;
	if prognum in ("MC") then do;
		if days_gap = days_in_mon then do; 
			month_full=0; month_gap=1; 
			end;
        else do; month_full=1; month_gap=0; days_gap=0;	
		end;   
		end;
	else do;
	   if days_gap > 0 then do; 
            month_full=0; month_gap=1; 
	   end; 
	   else do; month_gap=0; month_full=1; 
	   end;
	end;
	if last.mon then do;
	       days_elig = days_in_mon - days_gap;count=1;
	       output; 
	end;
run;

Re: Proc SQL equivalent for If first. then do if last.

SASKiwi — Mon, 06 Jun 2022 23:44:00 GMT

Unfortunately there isn't any. Nor is there any concept of conditional processing of rows. That functionality is available in SQL stored procedures - these are only available in external RDBMSs typically. There is no equivalent in SAS SQL. Why do you want to change to SQL?

Re: Proc SQL equivalent for If first. then do if last.

tarheel13 — Tue, 07 Jun 2022 01:20:47 GMT

he has the equivalent in his paper

https://support.sas.com/resources/papers/proceedings17/0930-2017.pdf

Re: Proc SQL equivalent for If first. then do if last.

Reeza — Tue, 07 Jun 2022 01:25:59 GMT

Not easily especially with a few of them.

It's much easier to do in a data step or a different language. Is SQL your only option? What flavour of SQL? Sometimes SQL variants have different methods of implementing this type of functionality.

For doing this code conversion, indenting your code also makes it much easier to read.

data hsshow(/*drop=days_span*/);
    set show_all; 
    by member_i prognum mon;    
    if first.mon then days_elig=0;
	days_elig + days_span;
    if days_elig gt days_in_mon then days_elig=days_in_mon;
	days_gap = days_in_mon - days_elig;
	if prognum in ("MC") then do;
		if days_gap = days_in_mon then do; 
			month_full=0; month_gap=1; 
			end;
        else do; month_full=1; month_gap=0; days_gap=0;	
		end;   
		end;
	else do;
	   if days_gap > 0 then do; 
            month_full=0; month_gap=1; 
	   end; 
	   else do; month_gap=0; month_full=1; 
	   end;
	end;
	if last.mon then do;
	       days_elig = days_in_mon - days_gap;count=1;
	       output; 
	end;
run;

Re: Proc SQL equivalent for If first. then do if last.

wbsjd — Tue, 07 Jun 2022 05:30:41 GMT

We are converting all our projects from Base SAS to SAS DI Studio. In SAS DI Studio, most transformations are based on SQL instead of data step. Thus I am trying to translate our code from SAS data step to Proc SQL. I think I will use User Written transformation in the SAS DI studio to include this data step, that works. Thank you for answering my question~