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

[wbsjd]

 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;

[SASKiwi]

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?

[tarheel13]

he has the equivalent in his paper

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

[Reeza]

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;

[wbsjd]

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~