DATA Step, Macro, Functions and more

Help with converting sas code to sql code

Reply
Occasional Contributor
Posts: 5

Help with converting sas code to sql code

[ Edited ]

I work in healthcare so importing sas and sql code here is a breach of security. Is there anyone I can talk to about it and possibly share screen of the codes?

Super User
Posts: 17,823

Re: Help with converting sas code to sql code

Tech support or hire a consultant who will sign a confidentiality agreement otherwise you're still violating security rules. 

 

However, you don't have to post real data and the logic won't violate security so make up data that looks like your data but is fake. The code is then generic and you're not violating any security requirements. You may be violating intellectual property if your company considers the code proprietary but that's unlikely.  Health care problems are not unique anyways...the same logic is applied in many other industries. 

Occasional Contributor
Posts: 5

Re: Help with converting sas code to sql code

Agreed.

 

Here's a sample sas code. I am trying to convert to read as sql code.

 

data PARTB_PHYS_LOOPY (drop = nat_key nat_key_count util_cnt_sum paid rename = paid2 = paid);

set  PARTB_PHYS_LOOPY;

by nat_key;

if (last.nat_key and util_cnt_sum >= 1 and nat_key_count > 1 )then paid2 = put(abs(CLM_LINE_NCH_PMT_AMT)*100+&count,$11.); else paid2 = paid;

run;

Super User
Super User
Posts: 7,401

Re: Help with converting sas code to sql code

I am sorry, what does "I work in healthcare so importing sas and sql code here is a breach of security." mean?

I also work in healthcare, and it is Patient data which is confidential.  Of course your code is property of your company but if you have a question, create some test data, just a few lines as a datastep, and the logic you wish to ask your quesiton on.  Otherwise we cannot help you.

 

"Is there anyone I can talk to about it and possibly share screen of the codes?"

This is a Q&A forum, if you need to do something sensitive, consult a contractor, sign NDA's etc.

Occasional Contributor
Posts: 5

Re: Help with converting sas code to sql code

Here's a sample sas code. I am trying to convert to read as sql code.

 

data PARTB_PHYS_LOOPY (drop = nat_key nat_key_count util_cnt_sum paid rename = paid2 = paid);

set  PARTB_PHYS_LOOPY;

by nat_key;

if (last.nat_key and util_cnt_sum >= 1 and nat_key_count > 1 )then paid2 = put(abs(CLM_LINE_NCH_PMT_AMT)*100+&count,$11.); else paid2 = paid;

run;

 

 

 

Super User
Super User
Posts: 7,401

Re: Help with converting sas code to sql code

Ok, well I can give you a few pointers, but that is going to take some working as SAS and SQL work differently.  Why do you need SQL anyways? There isn't much beneift if you are just running it through SAS, and if its on a database, then your better off using the DB's SQL syntax as it will have functions and such like.

SQL takes the form:

select    <- here is where you put the variables you want in the output dataset

from      <- the source of the data

where/group by/order by/having etc.  <- optional extras to do filtering and such like.

 

So this row:

data PARTB_PHYS_LOOPY (drop = nat_key nat_key_count util_cnt_sum paid rename = paid2 = paid);

You wouldn't need the drop as you only "select" the variables you want to keep.  And to rename you "select" from the original data into a new variable with <oldvar> as <newvar>.

The set command is replace and the data source is put in the from part.

 

This however:

by nat_key;

if (last.nat_key and util_cnt_sum >= 1 and nat_key_count > 1 )then paid2 = put(abs(CLM_LINE_NCH_PMT_AMT)*100+&count,$11.); else paid2 = paid;

 

Is pure SAS code.  SQL does not have this first/last syntax.  You need to identify the record to process this on using logic and the data available.  Also note, that if this goes to a database, put() abs() and such like would not work.  You could assign a incrementor value per by group, and take the max() of that to find last record for instance.

Occasional Contributor
Posts: 5

Re: Help with converting sas code to sql code

Thanks.

Super User
Posts: 17,823

Re: Help with converting sas code to sql code

Are you switching to SAS SQL or a different type, ie T-SQL or PL/SQL or MySQL? 


The hardest part is usually handling BY processing that may or may not factor in the FIRST/LAST logic but most of it (not all) can be implemented using WINDOW or PARTITION statements in the new flavours of SQL. When I first started out, no SQL had those features so SAS data step was much easier to work with. 

Occasional Contributor
Posts: 5

Re: Help with converting sas code to sql code

I am just using T-SQL.
Super User
Posts: 5,256

Re: Help with converting sas code to sql code

Uh-uh. If you are converting from SAS be prepared to hire any help you need. This site is mainly for devolving SAS applications, not dismantling them.
Data never sleeps
Ask a Question
Discussion stats
  • 9 replies
  • 119 views
  • 0 likes
  • 4 in conversation