proc sql into data step

[Geo-]

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;

[Kurt_Bremser]

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

[Geo-]

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

[Kurt_Bremser]

---

@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.

[LinusH]

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

[VDD]

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.

[mkeintz]

@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.

[VDD]

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.

[mkeintz]

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:

1. 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.
2. LAG functions (which aren't even supported in proc sql).
3. self-MERGEs to get lead values,
     e.g. MERGE have have (firstobs=2 keep=x rename=(x=xlead1));.
4. rolling windows for time series (sometimes better than the time-series-oriented PROC EXPAND)
5. 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.

[Tom]

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?

[Geo-]

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

[Kurt_Bremser]

---

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

---

Still no example data.

[Geo-]

Yes,I have made some samples in the attachment

[Kurt_Bremser]

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.