turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Help with converting sas code to sql code

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 10:21 AM - edited 05-26-2017 10:21 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 10:28 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 11:15 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 11:00 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 11:13 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 11:24 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 12:21 PM

Thanks.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 04:36 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 08:03 PM

I am just using T-SQL.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-26-2017 10:40 PM

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