topic Re: DATA STEPS TO SQL in SAS Programming

DATA STEPS TO SQL

however412 — Sat, 14 Nov 2015 21:14:14 GMT

have different data steps code, i need help to convert them to sql.

Could any one please help me with belwo data step codes to sql?

data w6.data3; set W6.data3;
data data4; set data3;
if SIC ge 6300 and SIC le 6350 then delete; /***Delete financial firms***/
if SIC ge 4900 and SIC le 4939 then delete; /***Delete utilities***/
if TACC_t eq . then delete;
if TA_t in (0, .) then delete;/***used to calculate ROA***/
if TA_tm1 in (0, .) then delete;/***used to calculate ROA***/
if Sales_t eq . then delete;
if Sales_tm1 eq . then delete;
if AR_ch_t eq . then delete;
if PPE_t eq . then delete;
if NI_t eq . then delete;
if NI_tm1 eq . then delete;
if AR_ch_t eq . then delete;
if MVE_tm1 eq 0 then delete; /*mused for extreme samples***/
Sales_ch_t = (Sales_t-Sales_tm1)/TA_tm1;
Sales_t=Sales_t/TA_tm1;
AR_ch_t=-AR_ch_t/TA_tm1;/***Note that accounts receivable change is (increase) decrease*/
Sales_rec_ch_t=Sales_ch_t-AR_ch_t;
if sales_rec_ch_t eq . then delete;
TACC_t=TACC_t/TA_tm1;
Int=1/TA_tm1;
EBEX_t=EBEX_t/TA_tm1;
CFO_t=CFO_t/TA_tm1;
PPE_t=PPE_t/TA_tm1;
ROA_t=(NI_t/TA_t);/***Current year net income over beginning total assets * */
ROA_tml=(NI_tm1/TA_tm1);/***Last year ROA***/
run;

proc sort data=data4; by SIC t;
data data4; set data4;
proc univariate noprint; var TACC_t;
output out=N n=N;
by SIC t; * t=yeara;
run;

data data5; merge data4 N; /***4-digit SIC code Eliminate SIC t combination with less than 10 obs*/
by SIC t; if N < 10 then delete;
run;

data data5; set data5;
proc reg noprint;
model TACC_t = Int Sales_rec_ch_t PPE_t ROA_t;
output out=pred11 p=ETAC1_t; /***r=DA0C1***/
model TACC_t = Int Sales_rec_ch_t PPE_t ROA_tm1;
output out=pred21 p=ETAC2_t /***r=DACC2***/;
by SIC t;
run;

data pred1; set pred1;
DACC1=TACC_t-ETAC1_t;
proc sort; by cusip t;
data pred2; set pred2;
DACC2=TACC_t-ETAC2_t;
proc sort; by cusip t;
run;

data data6; merge pred1 pred2;
by cusip t;
class=1;
run;

______________________________________________
data study control; set data6;
if Class eq 1 then output study;
if Class eq 1 then output control;
run;

Re: DATA STEPS TO SQL

PGStats — Sat, 14 Nov 2015 22:09:56 GMT

Do you mean SAS/SQL or some other version of SQL?

Re: DATA STEPS TO SQL

Reeza — Sat, 14 Nov 2015 22:48:10 GMT

Have you tried anything? This goes beyond the 'help' category and more like 'work' category.

Re: DATA STEPS TO SQL

FreelanceReinh — Sat, 14 Nov 2015 23:38:55 GMT

Welcome to the SAS Support Communities!

As you have noticed, it is technically possible to post the same question into different forums (such as "Base SAS Programming" and "General SAS Programming"). However, this is strongly discouraged, because people could think your question is unanswered while its copy has received a lot of response already.

So, you want to convert data step code to SQL. May I ask, why do you want to translate your program into a language that you don't speak?

Please consider that a typical program is revised many times until its final run. Therefore, I'd recommend that you do the editing in the existing code as this will be easier for you.

At least I can't believe that this is the final, translation-ready version of your program. For example, let's look at the last two data steps: The last but one step creates a work dataset DATA6 which contains a numeric variable CLASS whose value is 1 for each and every observation. The last data step looks as if it was to split DATA6 into two different datasets, STUDY and CONTROL, but in fact creates two exact copies of DATA6 by applying not only redundant, but obviously identical IF conditions to all observations of DATA6.

Or look at the second data step: The repetitive "if <var> eq . then delete" statements could be simplified to a single subsetting IF statement of the form "if n(<var1>, <var2>, ...)" and so on.

Moreover, your program is interspersed with PROC steps. Are these to be converted, too?

When you say "SQL", which implementation do you mean? PROC SQL? [Oh, I see, PG has asked this already while I was typing this post.]

I think it would help a lot if these issues and questions were resolved first.

Re: DATA STEPS TO SQL

however412 — Sun, 15 Nov 2015 00:16:59 GMT

yes, i mean sas sql

Re: DATA STEPS TO SQL

however412 — Sun, 15 Nov 2015 00:18:57 GMT

proc sql;
create table data w6.data3 as
select *
SIC ge = 6300 and SIC le = 6350 then delete
SIC ge = 4900 and SIC le = 4939 then delete
TACC_t = eq . then delete
TA_t in (0, .) then delete
TA_tm1 in (0, .) then delete
Sales_t = eq . then delete
Sales_tm1 = eq . then delete
AR_ch_t = eq . then delete
PPE_t = eq . then delete
NI_t = eq . then delete
NI_tm1 = eq . then delete
AR_ch_t = eq . then delete
MVE_tm1 = eq 0 then delete
Sales_ch_t = (Sales_t-Sales_tm1)/TA_tm1
Sales_t=Sales_t/TA_tm1
AR_ch_t=-AR_ch_t/TA_tm1
Sales_rec_ch_t=Sales_ch_t-AR_ch_t
sales_rec_ch_t = eq . then delete
TACC_t=TACC_t/TA_tm1
Int=1/TA_tm1
EBEX_t=EBEX_t/TA_tm1
CFO_t=CFO_t/TA_tm1
PPE_t=PPE_t/TA_tm1
ROA_t=(NI_t/TA_t)
ROA_tml=(NI_tm1/TA_tm1)

from w6.data3 ;

quit;

PROC SQL;
CREATE TABLE NOPRINT AS
SELECT*
where n=N
From data4 group by SIC t;
quit;

PROC SQL;
CREATE TABLE DATA5SS;
SELECT *
FROM DATA5, DATA4
WHERE SIC t; N < 10 then delete;
QUIT;

could you check this out? this what i have tried.

Re: DATA STEPS TO SQL

Patrick — Sun, 15 Nov 2015 01:36:00 GMT

What's the reason that you want to convert the data step into SQL?

Your SQL skills look rater "beginner level" given the following:

select *
SIC ge = 6300 and SIC le = 6350 then delete 
...

If you really want to use SQL code then I'd suggest that you probably first need to spend some time to skill-up in SQL syntax.

If you want to exclude rows then you need to formulate your condition as part of a WHERE clause. It has nothing lost within the SELECT clause.

Re: DATA STEPS TO SQL

FreelanceReinh — Sun, 15 Nov 2015 09:38:55 GMT

There is a nice conference paper PROC SQL for DATA Step Die-hards to get you started with PROC SQL. I know an older version of it.

Then, if you want to go further, I can recommend Howard Schreier's excellent textbook PROC SQL by Example.

Both of these resources contrast PROC SQL steps with similar DATA steps, which is ideal if you know the latter and want to learn the former.

Re: DATA STEPS TO SQL

RW9 — Mon, 16 Nov 2015 10:09:05 GMT

Just to add onto the good advice above, code formatting is also important. Your code is very hard to read. Use indentation, put each row on a new line, netsle blocks of code, finish blocks of code properly (e.g. run;). As a helpful tip, there is above the box you type in a series of icons, {i} and the run sas symbols alow you post code directly, this keeps any of the formatting.