About sas_student1

sas_student1 · ‎01-23-2018

Thanks, Here is a better example: All the elements are in one column. The values in each row are in order. That is, each row will start with 4 numbers a space and then 8 numbers. After that I know that there well be a provider type and there are a certain set of provider types that are listed only, here we see "Auto Enroll Provider", "original Provider", "Collapsed Provider" and "Genesis Provider" there are a few more but not listed in the example below. After the provider type the name of the provider is listed, I am not sure the length of the name it can be 20 characters, 35 characters or even longer. Then follows a three digit number After the number there is always a 02-02 followed by a date that is in a mmddyy10 format (although note that since all this is in one column the date is not formatted) Following the date will be a four digit number with a "-" and then a small description, not sure the length of the description. Finally there is a string of characters that always starts with "1-RT BE" This is what the sample looks like: 4278 01234567 Auto Enroll Provider FAMILY HEACARE 008 02-02 01/01/2018 131.23 0287-HSP OUT SR 1-RT BE 009-COSNF 4273 34876539 Original Provider BURKE CENTER 003 02-02 01/01/2018 187.01 0287-HSP OUT SR 1-RT BE 010-COSNA 1468 04876298 Collapsed Provider PHOENIX RISES OF NJ INC 005 02-02 01/01/2018 17.67 0160-D&TCS 1-RT BE 009-COSNF 1540 10983746 Genesis Provider COLLEGE PARK 016 02-02 01/01/2018 148.43 0160-D&TCS 1-RT BE 013-INVRATECD 1671 34098734 Auto Enroll Provider THIS CNTY DOH 004 02-02 01/01/2018 138.70 0287-HSP OUT SR 1-RT BE 040-INVREQ 1671 45398726 Auto Enroll Provider THAT CLINIC 004 02-02 01/01/2018 138.70 0287-HSP OUT SR 1-RT BE 009-COSNF And this is what I am trying to get to: Rate ID Provider NAME ProvID type Date amount Desc RT 4278 01234567 Auto Enroll Provider FAMILY HEALTHCARE 008 02-02 1/1/2018 131.23 0287-HSP OUT SR 1-RT BE 009-COSNF 4273 34876539 Original Provider BURKE HEALTH CENTER 003 02-02 1/1/2018 187.01 0287-HSP OUT SR 1-RT BE 010-COSNA 1468 04876298 Collapsed Provider PHOENIX RISES OF NEW JESRSEY INC 005 02-02 1/1/2018 17.67 0160-D&TCS 1-RT BE 009-COSNF 1540 10983746 Genesis Provider COLLEGE PARK 016 02-02 1/1/2018 148.43 0160-D&TCS 1-RT BE 013-INVRATECD 1671 34098734 Auto Enroll Provider THIS CNTY DOH 004 02-02 1/1/2018 138.7 0287-HSP OUT SR 1-RETR TO BE 040-INVREQ 1671 45398726 Auto Enroll Provider THAT CLINIC 004 02-02 1/1/2018 138.7 0287-HSP OUT SR 1-RT BE 009-COSNF Suggestions?

sas_student1 · ‎01-23-2018

Hello, I was given a dataset with about 6000 rows, unfortunately it was given to me in a text and was not formatted properly. I cannot use the @ or format options to create separate variables/columns for the one long string that was shared. What I need to do is to search for a specific set of characters for each row of the database and create a new variable that populates with the search. In this dataset all the elements are in one column. The values in each row are in order. That is, each row will start with 4 numbers a space and then 8 numbers. After that I know that there well be a provider type and there are a certain set of provider types that are listed only, here we see "Auto Enroll Provider", "original Provider", "Collapsed Provider" and "Genesis Provider" there are a few more but not listed in the example below. After the provider type the name of the provider is listed, I am not sure the length of the name it can be 20 characters, 35 characters or even longer. Then follows a three digit number After the number there is always a 02-02 followed by a date that is in a mmddyy10 format (although note that since all this is in one column the date is not formatted) Following the date will be a four digit number with a "-" and then a small description, not sure the length of the description. Finally there is a string of characters that always starts with "1-RT BE" This is what the sample looks like: 4278 01234567 Auto Enroll Provider FAMILY HEACARE 008 02-02 01/01/2018 131.23 0287-HSP OUT SR 1-RT BE 009-COSNF 4273 34876539 Original Provider BURKE CENTER 003 02-02 01/01/2018 187.01 0287-HSP OUT SR 1-RT BE 010-COSNA 1468 04876298 Collapsed Provider PHOENIX RISES OF NJ INC 005 02-02 01/01/2018 17.67 0160-D&TCS 1-RT BE 009-COSNF 1540 10983746 Genesis Provider COLLEGE PARK 016 02-02 01/01/2018 148.43 0160-D&TCS 1-RT BE 013-INVRATECD 1671 34098734 Auto Enroll Provider THIS CNTY DOH 004 02-02 01/01/2018 138.70 0287-HSP OUT SR 1-RT BE 040-INVREQ 1671 45398726 Auto Enroll Provider THAT CLINIC 004 02-02 01/01/2018 138.70 0287-HSP OUT SR 1-RT BE 009-COSNF And this is what I am trying to get to: Rate ID Provider NAME ProvID type Date amount Desc RT 4278 01234567 Auto Enroll Provider FAMILY HEALTHCARE 008 02-02 1/1/2018 131.23 0287-HSP OUT SR 1-RT BE 009-COSNF 4273 34876539 Original Provider BURKE HEALTH CENTER 003 02-02 1/1/2018 187.01 0287-HSP OUT SR 1-RT BE 010-COSNA 1468 04876298 Collapsed Provider PHOENIX RISES OF NEW JESRSEY INC 005 02-02 1/1/2018 17.67 0160-D&TCS 1-RT BE 009-COSNF 1540 10983746 Genesis Provider COLLEGE PARK 016 02-02 1/1/2018 148.43 0160-D&TCS 1-RT BE 013-INVRATECD 1671 34098734 Auto Enroll Provider THIS CNTY DOH 004 02-02 1/1/2018 138.7 0287-HSP OUT SR 1-RETR TO BE 040-INVREQ 1671 45398726 Auto Enroll Provider THAT CLINIC 004 02-02 1/1/2018 138.7 0287-HSP OUT SR 1-RT BE 009-COSNF Any suggestions would be great! Thanks

sas_student1 · ‎11-28-2017

Thank you! I will try this and thank you everyone for your suggestions and help. I did try a method that required a few steps. That is, in the two tables that I wanted to match I created a primary key of sorts for each row i.e id=_n_ which I would carry as I run an SQL query by the conditions I wanted to match the two data sets along with the time frame. This SQL creates a table with only the matched rows. Since I had the column with the primary key in this matched table I can go back to the original tables and identify which two rows were matched. I then run another SQL where I take out the matched rows from the original table. This method is not perfect as there are some duplicates, but it gets to what I need. Thank you all for your assistance!!

sas_student1 · ‎11-25-2017

Thank you PG, There is a possibility that consecutive dates are less than 7 days. Since I am dealing with thousands of rows. How do I not run into that problem? Thanks

sas_student1 · ‎11-25-2017

Sure, So here are the two original data set example: dataA; input client_id hosp_id hosp_loc date; informat date mmddyy10.; format date mmddyy10.; cards; 1 123 1234 10/1/2015 2 123 2345 10/1/2015 3 123 4325 2/3/2015 4 156 5678 5/6/2016 5 167 2345 10/11/2016 6 167 8907 1/4/2017 7 178 3456 1/31/2017 8 178 4321 1/31/2017 9 145 5467 7/23/2017 10 134 2398 8/24/2017 11 967 2897 9/13/2017; run; dataB; input client_id hosp_id hosp_loc date; informat date mmddyy10.; format date mmddyy10.; cards; 1 123 1234 10/1/2015 2 123 2345 10/5/2015 3 123 4325 2/1/2015 4 156 5678 5/20/2016 5 167 2345 10/11/2016 6 167 8907 1/4/2017 7 138 8643 1/31/2017 8 178 4321 2/5/2017 9 145 5467 7/23/2017 10 134 2398 8/24/2017 11 967 2897 9/13/2017 13 167 4567 12/31/2016 14 145 3345 11/23/2016 15 123 1234 11/12/2016 16 999 9999 3/24/2016 17 178 4321 2/3/2016; run; using the merge statement with the different In statement we would get the following table: Match 1 client_id Hospital_id Hospital_loc Service_date 1 123 1234 10/1/2015 5 167 2345 10/11/2016 6 167 8907 1/4/2017 7 178 3456 1/31/2017 9 145 5467 7/23/2017 10 134 2398 8/24/2017 11 967 2897 9/13/2017 Leaving us with the remaining two tables: Table DataA1 client_id Hospital_id Hospital_loc Service_date 2 123 2345 10/1/2015 3 123 4325 2/3/2015 4 156 5678 5/6/2016 8 178 4321 1/31/2017 And Table DataB1 client_id Hospital_id Hospital_loc Service_date 2 123 2345 10/5/2015 3 123 4325 2/1/2015 4 156 5678 5/20/2016 8 178 4321 2/5/2017 13 167 4567 12/31/2016 14 145 3345 11/23/2016 15 123 1234 11/12/2016 16 999 9999 3/24/2016 17 178 4321 2/3/2016 Now what I would want to do in this round is to take table A1 and B1 and match by the time condition so I would get the following matched table: client_id Hospital_id Hospital_loc Service_date 2 123 2345 10/1/2015 3 123 4325 2/1/2015 8 178 4321 1/31/2017 And should have two more tables that show the rows that have not been matched. so DataA2: client_id Hospital_id Hospital_loc Service_date 4 156 5678 5/6/2016 and DataB2: client_id Hospital_id Hospital_loc Service_date 4 156 5678 5/20/2016 13 167 4567 12/31/2016 14 145 3345 11/23/2016 15 123 1234 11/12/2016 16 999 9999 3/24/2016 17 178 4321 2/3/2016 Thanks again!

sas_student1 · ‎11-25-2017

Of course sometimes the simplest solution is the answer. However for issue two how do I create separate tables. So I join by the client ID, Hospital ID, Hospital Location and have the rule for the dates. But I then again want to create three databases so that if the two tables match by the above rule they are created in one table and if they do not match two separate tables are created. How do we put that rule in an SQL statement? Thanks

sas_student1 · ‎11-24-2017

Hello SAS community!!! I have been working on a SAS query all day today and am stuck. Thought to ask the much smarter people here for help. I have two datasets, let’s call them dataA and DataB. Among many other variables that are different in the two-data set, they both have the same 4 common variables, Client ID (client_id), service date (srdate), Hospital id (hosp_id) and hospital Location (hosp_loc). DataA has about 600,000 rows and DataB has about 800,000 rows. There are 2 issues that I have: I want to match common rows by the above 4 elements but in 4 different series. That is. any rows that have the same client ID, hospital Location, Hospital Id and Service date in both dataset I want to match and move to one dataset while the rows that do not match I want to separate them down to two separate non-matched datasets. That is, non-match dataA is saved in a dataA1 table and non-match dataB is saved in a dataB1 table. Now I can do, via either a simple merge with a by statement of the 4 variables or a join via proc SQL to create a matched table (table match1), but am having trouble (issue 1) separating the two non-matched tables into two separate tables. Issue 2: Another issue is that after the full merge above for the remaining rows that did not match I want to have two separate tables (i.e. DataA1 and DataB1). Here what I want to do is match again by client Id, hospital Id, hospital location, but this time if the service date in dataA1 is either 7 days before or 7 days after the service data in dataB1 then I want that to match and create a separate match2 table from above and then create two more non-matched tables for the remaining dataA1 and DataB1 tables (we call them DataA2 and Data B2). At the end I will union all the possible matched tables into one. Suggestions on how to tackle the two issues would be greatly appreciated. Thank you!

sas_student1 · ‎06-01-2017

Hello @art297, I was just sending you a note when I noticed your reply. You are correct @mkeintz 2nd suggestion actually yielded the result I needed. Actually I was trying your code too (which was cool and i wanted to try it) but it didnt quite give me the results I needed. For example: if I have a table of admit and disch of the client below client admit disch 1 3/5/2016 3/8/2016 1 3/10/2016 3/12/2016 1 4/3/2016 4/8/2016 1 4/19/2016 4/21/2016 1 4/23/2016 4/24/2016 1 5/23/2016 5/24/2016 1 6/1/2016 6/6/2016 1 12/23/2016 12/29/2016 I need the following rows to be output: client admit disch 1 3/5/2016 3/8/2016 1 4/3/2016 4/8/2016 1 5/23/2016 5/24/2016 1 12/23/2016 12/29/2016 @mkeintz 2nd suggestion does that however when I run the code you provided I dont get the exact rows I get: client admit disch 1 3/5/2016 3/8/2016 1 3/10/2016 3/12/2016 1 4/23/2016 4/24/2016 1 6/1/2016 6/6/2016 1 12/23/2016 12/29/2016 here is the full tables with the coloumns "needed", "mkeintz" and "art", the x under "mkeintz" and "art" indicate rows that were outputed using the respective codes and the x under the "needed" coloumn are as you may have guessed the rows that were needed: client admit disch needed mkeintz art 1 3/5/2016 3/8/2016 x x x 1 3/10/2016 3/12/2016 x 1 4/3/2016 4/8/2016 x x 1 4/19/2016 4/21/2016 1 4/23/2016 4/24/2016 x 1 5/23/2016 5/24/2016 x x 1 6/1/2016 6/6/2016 x 1 12/23/2016 12/29/2016 x x x trying to figure out where the code needs to be tweaked. maybe in the first. last. Thanks again to all

sas_student1 · ‎06-01-2017

Thank you Ksharp for your reply. I tried this and it didnt get the result I needed. I ran the following (Note I fixed row 2 admit date as it was after the disch date originally which was incorrect on my part) data have; input client provider (admit disch) (:mmddyy.); format admit disch mmddyy10.; datalines; 1 3 2/8/2016 2/10/2016 1 3 2/16/2016 2/22/2016 1 4 2/23/2016 2/24/2016 2 1 1/2/2016 1/3/2016 2 3 3/20/2016 3/23/2016 2 4 4/25/2016 4/26/2016 ; run; data want; merge have have (firstobs=2 keep=client admit rename=(admit=next_admit client=_client)); if client=_client and (next_admit-disch)>=15; run; I get the following: client provider admit disch _client next_admit 2 1 1/2/2016 1/3/2016 2 3/20/2016 2 3 3/20/2016 3/23/2016 2 4/25/2016 which is not what I need. I need rows 1 for client 1 and all three rows for client 3. But a big thank you for replying! I think mkeintz and possibly art297s suggestions may have worked! Testing it on my actual and bigger database! Thank you ALL for your help, I love asking questions here and getting many useful and helpful responses!!! I really appreciate it! You are all fantastic!!

sas_student1 · ‎05-31-2017

I have follow-up and clarifcaiton to my question, Apologies for being confusing. To clarify, what I want to do is if a client has multiple visits to a provider any visits that are within 15 days of each other to only take the first visit. So if a client is discharged on Jan 1 and then seen again within 15 days (so between Jan 2 and Jan 15) then only to include the Jan 1 visit. Then if a client is seen on Jan 16 then to include the Jan 16 visit as well. My example table was not the best. Actaully according to the above description then from my example table I should be expecting row 1 for client 1 and all three rows for client 2. I did try both ways that were suggested and it kind of worked. So when I have the data set as follows: (note that row 2 the admit date is before the discharge date which is wrong as zdeb had correctly pointed out) 　 data x; input client provider (admit disch) (:mmddyy.); format admit disch mmddyy10.; datalines; 1 3 2/8/2016 2/10/2016 1 3 2/26/2016 2/22/2016 1 4 2/23/2016 2/24/2016 2 1 1/2/2016 1/3/2016 2 3 3/20/2016 3/23/2016 2 4 4/25/2016 4/26/2016 ; run; I get the output where it gives me back rows 1, 4 and 5. NOTE: I also need row 6 for client 2 to also output. However, if I change the admit date on row two from '2/26/16' to '2/11/16' then I only get rows 4 and 5 from client 2 and I dont get any rows from client 1 back. I would want to get row 1 from client 1 still. To clarify, there were mistakes in my example. I would want row 1 from client 1 and all three rows from client 2, since what I am trying to do is that if there are multiple visits of a cilent and those multiple visits are all within 15 days of each other then to only give me the first vist record, however if the visits are greater than 15 days from each other then to give me each visit that is greater than 15 days. Hope this makes more sense and apologies for any confusion. Suggestions to the code that you had shared would be greatly appreciated! Thank you very very much! Appreciate it!

sas_student1 · ‎05-31-2017

Hello SAS community, I have a question regarding how to capture certain rows that fall under a certain criteria. For example I have a database that has clinical information on clients. (Client 1 and 2 in this example). I also have which provider they saw and what their admission (Admit Date) and Dishcarge (dsch date) dates are. I want to only keep the first rowe where the difference between the discharge date and the next admit date (for the same client) is less than 15. As such I would keep only row 1 (for client1) and rows 4 and 5 for Client 2. Suggestions on SAS code that can get this output? Row # Client Provider Admit Date Dsch Date 1 1 3 2/8/2016 2/10/2016 2 1 3 2/26/2016 2/22/2016 3 1 4 2/23/2016 2/24/2016 4 2 1 1/2/2016 1/3/2016 5 2 3 3/20/2016 3/23/2016 6 2 4 4/25/2016 4/26/2016 Thank you in advance!

sas_student1 · ‎03-26-2017

Hello, I have a question on how to use a merge statement in SQL. That I would like to "convert" the following merge statment in SQL. data want; merge have1 (in=a) have2 (in=b); by var1; if a=1 and b=1 then match='All'; else if a=1 and b=0 then match='have1'; else if a=0 and b=1 then match='have2'; run; I know one can use the where the statement in SQL but that would be equivalent to the match='All' statement above, right? I want to also be able to see where there is data in one table but not the other and vise versa? Is it a join statment? But I also would like to create a new varaible like 'match' so it can flag the rows that match between two datasets and those that dont. Any recommendation would be great! Thanks

sas_student1 · ‎01-13-2017

Thank you again for your reply. Looks like I have a lot to read up on. I'll look at the links to see if I can get some clues on how to do this. If this works this will help with my work greatly! Thank you

sas_student1 · ‎01-13-2017

Thank you for your reply, I will look at the links to respond to your questions in purple: Does it mean that you have installed SAS on a PC with windows OS ? --> I have SAS installed in both PC with windows and SAS installed in UNIX. What kind of data do you have on your UNIX computer ? --> I have different data in UNIX computer, from .dat files to excel files (that were FTPd from windows) and sas permanent data sets. Is it a UNIX SAS server ? --> I dont belive it is a UNIX SAS server but will have to check again. You said you are a self lerner. --> I meant self learner in codes and commnads. Softwares were installed on my compter for work. Does it mean you installed SAS University Edidion on your PC ? On the UNIX computer ? Do you have SAS installed on both - PC and UNIX ? -> SAS is installed in both PC and UNIX. Thank you again and I will reserach the links.

sas_student1 · ‎01-12-2017

I checked and unfortunatley in SAS UNIX I dont have SAS/CONNECT. However, what if I just want to connect to unix to get a file over to windows? Kind of like an FTP over, then do I still need to have both systems have SAS/CONNECT? I am not wanting to run SAS command in UNIX via PC SAS. Another contributer suggested this link : https://support.sas.com/documentation/cdl/en/lestmtsref/69738/HTML/default/viewer.htm#p0v0ijxl1k6d4bn16cshtic7u4i3.htm and example 2 in this link is I belive what I need, but it is still not clear. Any help and suggestions would be great! Thank you

Online Status	Offline
Date Last Visited	‎11-24-2024 01:07 AM

Re: how to add dates in an array

how to add dates in an array

How to get the previous record date

Re: How to import a CSV file from a user and password protected websit...

How to import a CSV file from a user and password protected website.

Re: How to add missing dates in a dataset

How to add missing dates in a dataset

Proc import issue where the data is both comma separated & also the va...

Re: How to extract ICD 10 codes from a website

Re: How to extract ICD 10 codes from a website

Re: How to count and categorize within in two groupings

Re: how to consolidate rows and get the last row by client

Re: how to count and output specific number of strings in a variable

Re: How to keep data rows using conditional rules

Re: How to keep data rows using conditional rules

How to combine two datasets while aggregating visits from one dataset

Re: How to add a footnote with asterisk to an excel table using ODS

Re: How to create a string of values with a condition

Re: How to connect SAS to MS Access query

How to keep data rows using conditional rules

Re: how to serach for specific text within a string and create a new v...

how to serach for specific text within a string and create a new varia...

Re: How to merge two datasets and create three seperate datasets.

Re: How to merge two datasets and create three seperate datasets.

Re: How to merge two datasets and create three seperate datasets.

Re: How to merge two datasets and create three seperate datasets.

How to merge two datasets and create three seperate datasets.

Re: How to keep data rows using conditional rules

Re: How to keep data rows using conditional rules

Re: How to keep data rows using conditional rules

How to keep data rows using conditional rules

SAS merge statement equivanet in SQL

Re: How to connect to UNIX server using PC SAS

Re: How to connect to UNIX server using PC SAS

Re: How to connect to UNIX server using PC SAS