BookmarkSubscribeRSS Feed
daddis2
Calcite | Level 5

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?

9 REPLIES 9
Reeza
Super User

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. 

daddis2
Calcite | Level 5

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

daddis2
Calcite | Level 5

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;

 

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

daddis2
Calcite | Level 5

Thanks.

Reeza
Super User

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. 

daddis2
Calcite | Level 5
I am just using T-SQL.
LinusH
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1923 views
  • 0 likes
  • 4 in conversation