About mayasak

mayasak · ‎07-12-2022

Thank you, Maguiremq. This worked perfectly fine. I'm trying to merge those 3 tables into one table (TB1) and then merge TB1 with another table (NHS) that I import either as a SAS file or an excel file. The issue is that I don't have the same key/identifier in all tables. The main table is the disease_incidence which can be joined to other tables with different key identifiers. So I'm wondering how I can streamline this merge. Thank you

mayasak · ‎07-12-2022

Hi, I want to import multiple Excel sheets into SAS and merge them into one file using macro. I wrote a macro but each call of the macro creates/overwrites the same dataset. Is there a way to keep the worksheets and merge them into one file? Here's the macro: proc import out= PQS replace datafile = "C:\Users\c227466\Desktop\Testing Testing/PQImport.xlsx" dbms = Excel; sheet = "&sheet"; getnames = yes; run; %mend data; %data(disease incidence); %data(efc); %data(lab info); Thank you

mayasak · ‎06-23-2022

Thank you ballardw. This helps. I'll try this. Regarding the data import automation, is there any source that can help me create a program that starts up every time I start SAS. I have no clue on how to do this. Thank you

mayasak · ‎06-23-2022

Hi ballardw, Sorry, I just pressed on the solution tab instead of a reply. I'm not sure if you get my reply now. The names of the tables are just the same and do not change - I have a little issue there that the names have spaces and are not supported by SAS, but I'm trying to solve this through Access because I'm not sure if I can do that through SAS. Both approaches that you recommended work for me. So is there any resource that help me in the programs you've mentioned? Thank you

mayasak · ‎06-23-2022

Hi, I tried to use the following codes (from a website) to import 10 MS Access files and both worked but there were two issues: I have to run the codes for each of the10 files (2 tables each) separately. This has to be done on a daily basis. My questions: Is there a way to manipulate the codes to automate the code/s to import all the tables together? Can I automate the process so that I don't have to manually import the files daily? Here are the 2 codes below: 1. proc sql; connect to odbc as savesdb (required="driver=Microsoft Access Driver (*.mdb, *.accdb); dbq=c:desktop\saves.accdb;"); create table table1_copy as select * from connection to savesdb (select * from table1); disconnect from savesdb; quit; 2. libname savesdb odbc required="driver=Microsoft Access Driver (*.mdb, *.accdb); dbq=c:path\saves.accdb;"; data table1_copy; set savesdb.table1; run; Thank you

mayasak · ‎06-21-2022

So, can the import from Access to SAS be automated? That is when new data are added to Access, it will be reflected automatically in SAS. The other issue is that I do a lot of data manipulation in excel, and by skipping this, I have to do all the work in SAS. Is there a way to automate this step (data cleaning, deduplicating, and merging) in SAS? Thank you

mayasak · ‎06-21-2022

Hi Kurt, Yes, that's what I think is the case. So most probably the automation should be done after this step, if possible. Thank you

mayasak · ‎06-21-2022

Hi Kurt, The websites are not public. I have no clue if it's possible to pull the data from the websites to SAS. The first one needs my org email and a password to access. When I get the report from this website, I only have one option, MS Access to export the data. The second one needs to access through a third-party user name and password and then unlock an identity code token. This website has SAS option for the export but I need to generate a "new data set" step to update the data before export. Thank you

mayasak · ‎06-21-2022

Hi Patrick, Thank you Patrick for the quick response. Here are my answers to your questions. Is SAS running on the same platform (computer) than Access or Excel? Yes, it is running on the same computer. 2. Do you have SAS Access to PC Files licensed. Yes, I have SAS Access to PC files licensed 3. IF SAS runs on the same platform than Access: Would it be an option for SAS to directly read from MS Access? Or would it even be an option for SAS to pull the information directly from the website? For this, I would definitely prefer the process in the following order: SAS to pull the information directly from the website but I need guidance on how to do that, if possible! SAS to pull data from MS Access, but I have to do cleaning, deduplicating and merging the data in SAS. Use Excel between SAS and Access and do the cleaning and deduplication in Excel then import the data in SAS. Thank you

mayasak · ‎06-21-2022

Hi, I'm working on a project that needs to import data from 2 websites to MS Access on a daily basis. The data sets are exported from Access to excel and then merged into 4 datasets. The files also will be exported from Excel to SAS, cleaned, deduplicated, matched, and merged into one data set then exported back to excel. My question - Is there any code that I can use to automate data import to SAS/ export to excel and/or any part of the data manipulation. Thank you

mayasak · ‎06-06-2022

Hi, I have two data sets that I need to merge Set1 and Set2. Matching variables that can be used are MRN Full_Name DOB and specimen_Date. The issue that I have is that set1 have about one third MRNs (complete in set2) missing. Set 2 have more than 1/3 missing Full_name (complete in set2). More than one person (obs) may have the same DOB and/or specimen_Date within each data set. So I'm leaning on using MRN and Full_Name but I'm not sure how to address the issue of missing data! I'm wonderin if this works: data merged; merge set1 set2 (in = in2); by mrn full_name; if in2; run; Thank you

mayasak · ‎05-31-2022

Hi AMSAS, Thank you first for your reply. This is what I get when I run the code (below): What I wanted exactly to keep the first observation for a person ( same dob, first_name, and last_name) then: - remove all other obs with specimen_date less than the first (previous) specimen_date by 12 months (1 year). - keep the obs with specimen date different than the previous specimen date by more than 12 months (1 year). So 323 Donald Duck with 01/06/2020 should stay as the one first specimen date and the rest should be removed because both have specimen dates less than 12 months difference from the first obs (03/06/2020 and 03/08/2020). * I noticed this happening when there is one or more obs with the same dob (same for Donald Duck) and missing names such as : 230 M . . 02/12/1956 03/09/2020 344 M . . 02/12/1956 04/09/2020 For Betty, you're right it should not show since it has a difference of less than one year from the previous obs. My mistake, sorry about that. Thank you, Obs Id sex first_name last_name DOB specimen_Date1234567 322 F Minnie Mouse 02/12/1956 08/06/2019 167 F Betty Boop 09/01/1993 03/06/2019 333 F Betty Boop 09/01/1993 03/30/2020 230 M 02/12/1956 03/09/2020 123 M Mickey Mouse 01/08/1961 01/01/2018 123 M Mickey Mouse 01/08/1961 09/02/2020 325 M Daffy Duck 07/01/1993 05/06/2020

mayasak · ‎05-31-2022

I have a data set (sample below). I need to remove duplicates with a condition on specimen_date. If specimen_date difference between observations for the same person was less than 12 months then remove, else keep. data have; input Id sex $ first_name $ last_name $ DOB :mmddyy10. specimen_Date :mmddyy10.; format DOB mmddyy10. specimen_date mmddyy10.; datalines; 123 M Mickey Mouse 01/08/1961 01/01/2018 123 M Mickey Mouse 01/08/1961 09/02/2020 322 F Minnie Mouse 02/12/1956 08/06/2019 344 M Donald Duck 02/12/1956 03/06/2020 344 M Donald Duck 02/12/1956 03/08/2020 323 M Donald Duck 02/12/1956 01/06/2020 323 M Daffy Duck 07/01/1993 09/06/2020 325 M Daffy Duck 07/01/1993 05/06/2020 333 F Betty Boop 09/01/1993 03/30/2020 167 F Betty Boop 09/01/1993 03/06/2019 245 F Betty Boop 09/01/1993 04/30/2020 167 F Betty Boop 09/01/1993 11/03/2021 344 M . . 02/12/1956 03/09/2020 344 M . . 02/12/1956 04/09/2020 ;;;; run; I used this code but I did not get the intended data: proc sort data=have out=have_sorted; by sex dob first_name last_name specimen_date; run; data want (drop=_:); set have_sorted; by sex dob first_name last_name; retain _maxdate; if first.dob then call missing(_maxdate); if specimen_date-360<=_maxdate then delete; _maxdate=specimen_date; run; This is what I'm supposed to get but I'm not getting the bolded ones: 123 M Mickey Mouse 01/08/1961 01/01/2018 123 M Mickey Mouse 01/08/1961 09/02/2020 322 F Minnie Mouse 02/12/1956 08/06/2019 344 M Donald Duck 02/12/1956 01/06/2020 325 M Daffy Duck 07/01/1993 05/06/2020 333 F Betty Boop 09/01/1993 09/31/2020 167 F Betty Boop 09/01/1993 03/06/2019 167 F Betty Boop 09/01/1993 11/03/2021 344 M . . 02/12/1956 03/09/2020 Any help much appreciated. Thank you

mayasak · ‎05-23-2022

Hi, How about working on deleting the obs using 2 steps. Meaning removing or retaining the second obs depending on the first step coding and then removing or retaining the third obs depending on a second step coding. Thank you.

mayasak · ‎05-16-2022

Hi Tom, Hi Tom, The one-year window is from the previous obs (test). For example, if the person had an obs on 1/5/2019 (first, kept always). All other obs that happened before 1/5/2020 should be removed. If the second obs was on 6/8/2020, it should be kept if no other obs happened after it or if the third one is also 1 year apart from the second, such as on 7/9/2022. In this case, we keep the three. If the third one was on 7/9/2020, only the first (1/5/2019 and the last 7/9/2020 should be kept and the second obs 6/8/2020 should be removed. Thank you.

Online Status	Offline
Date Last Visited	‎02-20-2024 03:05 PM

Re: Code for data aggregation and sum

Code for data aggregation and sum

Missing data when importing data from individual hospitals then aggreg...

Import Excel files (64 bit) into SAS (32 bit)

Re: Efficient way for if, else if, then do statements

Re: Efficient way for if, else if, then do statements

Efficient way for if, else if, then do statements

Re: Remove detected outliers from dataset

Remove detected outliers from dataset

Re: Macro for Proc Freq with several variables

Re: data merge

Re: data merge

Re: data merge

Re: Data input error

Re: Proc Freq output

Format phone numbers

Re: Import and merge multiple sheets from excel

Import and merge multiple sheets from excel

Re: Update MS Access table in SAS daily import

Re: Update MS Access table in SAS daily import

Update MS Access table in SAS daily import

Re: Automate excel files import to SAS

Re: Automate excel files import to SAS

Re: Automate excel files import to SAS

Re: Automate excel files import to SAS

Automate excel files import to SAS

Merge two data sets based on more than one variable

Re: Help dedup dataset based on date condition

Help dedup dataset based on date condition

Re: How to deduplicate data based on various conditions

Re: How to deduplicate data based on various conditions