topic Re: proc sql into data step in SAS Programming

proc sql into data step

Geo- — Mon, 19 Nov 2018 23:18:40 GMT

Hi~Anyone who is good at sas please trun sql in data step.

create table tableD nologging as
select a.acct,
b.app_dt,
case when a.acct in (select acct from tableC) then 1 else 0 end prom_ind
from tableA a
inner join tableB b
on a.application_no = b.application_no
where to_char(b.app_dt,'yyyymmdd') between '20150101' and '20150630'
;
quit;

Re: proc sql into data step

Kurt_Bremser — Sat, 17 Nov 2018 07:30:38 GMT

Please provide examples (data steps with datalines) for tablea, tableb and tablec.

Re: proc sql into data step

Geo- — Sat, 17 Nov 2018 08:07:53 GMT

hi expert,only looking for code the right with SAS data step syntax is fine.

Re: proc sql into data step

Kurt_Bremser — Sat, 17 Nov 2018 08:10:31 GMT

@Geo- wrote:

hi expert,only looking for code the right with SAS data step syntax is fine.

Code is driven by the data. Help us to help you.

Re: proc sql into data step

LinusH — Sat, 17 Nov 2018 11:29:58 GMT

Why do you want to translate the code?
Any particular problem with existing one..?

Re: proc sql into data step

VDD — Sat, 17 Nov 2018 13:34:25 GMT

Why do you want to change a working process into a possible POS.

If it works leave it alone. Why make a sports car run like a VW.

Re: proc sql into data step

mkeintz — Sun, 18 Nov 2018 00:06:05 GMT

@VDD

There may be many good reasons for converting proc sql code to data step code.

One of the most common is to take advantage of situations in which tables to be joined based on equality of variables arise from data sets sorted on the respective join variables. This would almost always be a speed enhancer for large data sets.

The data step might be the sports car.

Re: proc sql into data step

VDD — Sun, 18 Nov 2018 04:26:53 GMT

thank you @mkeintz that makes since. In the programming that I do I find myself using the datastep about 70 percent of the time and I never really know why I used it so much. Others always ask me why I do it in the datastep when I could use a case statement but your answer must reflect inner knowledge that I just not aware of but spontaneously do.

Re: proc sql into data step

Tom — Sun, 18 Nov 2018 15:31:58 GMT

You need to show the data.

Are ACCT and APP_DT variables in TABLEA or TABLEB? Are TABLEA and TABLEB sorted by APPLICATION_NO? How does relationship between ACCT and APPLICATION_NO work? Is ACCT a unique key on TABLEC? Does TABLEC have an index on ACCT?

Re: proc sql into data step

mkeintz — Sun, 18 Nov 2018 17:30:09 GMT

I would say, as a general rule, if the data sets are sorted advantageously, that most data step code utilizing that order is likely to be faster than the equivalent proc sql. That includes:

MERGE (or SET) with BY statement (where the by is utilizing the physical record sequence instead of data set index on the by variable), which I mentioned earlier.
LAG functions (which aren't even supported in proc sql).
self-MERGEs to get lead values,
e.g. MERGE have have (firstobs=2 keep=x rename=(x=xlead1));.
rolling windows for time series (sometimes better than the time-series-oriented PROC EXPAND)
interpolating observations.

Edited addition: Also, in cases where use of arrays are beneficial in data steps, I am not aware of an analogous coding structure in proc sql. But my knowledge of proc sql is quite inferior to my knowledge of the data step, so it might be out there in the documentation. If it is, I'd welcome a pointer.

Re: proc sql into data step

Geo- — Mon, 19 Nov 2018 14:23:32 GMT

hi,expert~I have updated the question,please take a look

Re: proc sql into data step

Kurt_Bremser — Mon, 19 Nov 2018 14:33:37 GMT

@Geo- wrote:
hi,expert~I have updated the question,please take a look

Still no example data.

Re: proc sql into data step

Geo- — Mon, 19 Nov 2018 23:24:47 GMT

Yes,I have made some samples in the attachment

Re: proc sql into data step

Kurt_Bremser — Tue, 20 Nov 2018 10:53:36 GMT

Excel files are NOT SAS datasets. They can tell us nothing about column attributes like lengths, formats etc, as those informations are mostly lost during the export.

Please supply example data as advised.