Help using Base SAS procedures

SAS TO SQL

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,041
Accepted Solution

SAS TO SQL

Hi ,

Could some one help me with the equivalent SQL code ???

this code in basic SAS is mainly merges and a couple of transposes

thanks

Hi,

Thanks for your inputs.

I want to take back my question

Regards


Accepted Solutions
Solution
‎06-09-2014 05:41 AM
Super User
Super User
Posts: 7,984

Re: SAS TO SQL

Posted in reply to robertrao

Hi,

What precisely do you want help on as I am not writing all that code.  Also, why do you need to convert it to SQL, is it to move it to a database, if so then you need to investigate the database syntax as they have additional functionality which will help.  The only thing at a quick glance that I would suggest being difficult is the transpose as SQL is built to work on normalized tables rather than transposed.

One final thing.  You may be quicker looking at your final datastep and working back from there e.g.

data final(drop=     /* This becomes proc sql; create table final as

set location; /* move to end */

/* These are your select statements */

SEX=input(put(gender_name,$sex.),3.);

RACE=input(put(Ethnic_Group,$race.),3.);

PAY1=input(put(Financial_Class,$payer.),3.);

HOSPID=input(put(Company_Code,$hosp.),4.);

DRG=MSDRG_CODE*1;

POINTOFORIGINUBO4=put(Admit_Source_Code,$Porign.);

MDC=input(put(DRG,MDC.),best.);

/* change to case statemtn */ if MSDRG_code="N/C" then MSDRG_code="000";

/* add in here locations for each of the above variables */

/* and join them */

run;

Note you can drop all the sort statements as SQL doesn't require pre-sorted data like SAS.  Depending on your platform, the formats will only work in SAS.  My choice is to remove that and put your formats in another dataset and use that as lookup e.g.

(select THIS.CODE_VALUE from CODE_DATASET THIS where THIS.CODE=BASE.CODE) as LONG_VALUE,

One final thing, you use arrays at a point in your table, you would need to re-think your logic there as (I mentioned before) SQL is based on normalized (or data going down rather than across) tables.  So you may struggle to get the data going across, or have the functionality to process data across.

Take it bit by bit also

View solution in original post


All Replies
Solution
‎06-09-2014 05:41 AM
Super User
Super User
Posts: 7,984

Re: SAS TO SQL

Posted in reply to robertrao

Hi,

What precisely do you want help on as I am not writing all that code.  Also, why do you need to convert it to SQL, is it to move it to a database, if so then you need to investigate the database syntax as they have additional functionality which will help.  The only thing at a quick glance that I would suggest being difficult is the transpose as SQL is built to work on normalized tables rather than transposed.

One final thing.  You may be quicker looking at your final datastep and working back from there e.g.

data final(drop=     /* This becomes proc sql; create table final as

set location; /* move to end */

/* These are your select statements */

SEX=input(put(gender_name,$sex.),3.);

RACE=input(put(Ethnic_Group,$race.),3.);

PAY1=input(put(Financial_Class,$payer.),3.);

HOSPID=input(put(Company_Code,$hosp.),4.);

DRG=MSDRG_CODE*1;

POINTOFORIGINUBO4=put(Admit_Source_Code,$Porign.);

MDC=input(put(DRG,MDC.),best.);

/* change to case statemtn */ if MSDRG_code="N/C" then MSDRG_code="000";

/* add in here locations for each of the above variables */

/* and join them */

run;

Note you can drop all the sort statements as SQL doesn't require pre-sorted data like SAS.  Depending on your platform, the formats will only work in SAS.  My choice is to remove that and put your formats in another dataset and use that as lookup e.g.

(select THIS.CODE_VALUE from CODE_DATASET THIS where THIS.CODE=BASE.CODE) as LONG_VALUE,

One final thing, you use arrays at a point in your table, you would need to re-think your logic there as (I mentioned before) SQL is based on normalized (or data going down rather than across) tables.  So you may struggle to get the data going across, or have the functionality to process data across.

Take it bit by bit also

Respected Advisor
Posts: 4,173

Re: SAS TO SQL

Posted in reply to robertrao

I believe you need to ask a bit more educated and targeted questions. You can't really expect anyone to just do all the work for you.

You would also need to explain why this needs to be done using SQL.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 238 views
  • 0 likes
  • 3 in conversation