About ybz12003

ybz12003 · ‎12-18-2024

It's not like that, I fixed it by adding the option to each sheet output option.

ybz12003 · ‎12-18-2024

Hello, I have the following codes for the Excel file output. However, the title and footnote aren't shown in the Excel file. Please help. %let bdate1=2004_01_14; /* The most updated date to extracted LIVD table */ %let bdate2=2014_02_17; /* The most updated date to extracted LOOKUP table */ %let footie=%str(LIVD as of &bdate1. & LOOKUP as of &bdate2.); %let tdate=%sysfunc(today(), yymmddn8.); /* Generate today's date for filename */ ods excel file="Pathway\LIVD_LOOKUP_check_&tdate..xlsx"; options(sheet_interval="none" embedded_titles="yes" embedded_footnotes="yes" flow="tables"); ods excel options(sheet_name="LIVD"); Proc print data=LIVD_; Title "LIVD - &bdate1."; run; ods excel options(sheet_name="LOOPUP"); Proc print data=test_lookup; title "LOOKUP - &bdate2.";; run; ods excel options(sheet_name="Unmatched_LIVD"); Proc print data=Unmatched_LIVD; title "Codes in LIVD but not in LOOPUP"; footnote "&footie"; run; ods excel options(sheet_name="Unmatched_LOOPUP"); Proc print data=Unmatched_Lookup; title "Codes in LOOPUP but not in LIVD"; footnote "&footie"; run; ods excel close;

ybz12003 · ‎11-23-2024

Apologies for any confusion. In the long text from row 1, 'Herpes Simplex Virus (HSV) 1/2 (IgG) Type Antibody, CSF Lymphocytic Choriomeningitis (LCM) Virus (IgG, IgM) Antibody, IFA, CSF Measles (Rubeola) (IgG, IgM) Antibody Panel, IFA, CSF Mumps Antibody Panel, IFA, CSF Varicella-Zoster Virus (VZV) Antibodies (Total, IgM), ACIF/IFA, CSF West Nile Virus (WNV) Antibodies (IgG, IgM), CSF' I would like to use 'IgG', 'IgM', or 'IgG, IgM' as labels. If a pathogen appears before the label, I will add an underscore between the pathogen and the antibody type to create a new pathogen_antibody label. For example: Herpes Simplex Virus (HSV) appears before 'IgG', so the first label will be HSV_IgG . The second label is 'IgG, IgM' and appears after Measles, so the second label is Measles_IgG, IgM . (BTW, either Meals or Rebeola is fine cause they are in the same group) Mumps does not have a label associated with it, so it will not be included in the results. Ideally, I would like each result to be listed in a separate row. However, if that's not possible, listing them all in one row would be acceptable. Also, as the text shows, not all pathogen names have abbreviations or are enclosed in parentheses. Using the full names for the results would be more accurate. Additionally, I noticed that 'CSF' is the last word associated with each pathogen. To improve the search, it might be helpful to use 'CSF' as a separator to distinguish the next pathogen. data Want_Final; length Sub_Analyte $1000; infile datalines delimiter='#'; input Sub_Analyte; datalines; Herpes Simplex_IgG# Lymphocytic Choriomeningitis_IgG,IgM# Measles_IgG,IgM# Varicella_IgM# West Nile Virus_IgG,IgM# Herpes Simplex_IgG# ;

ybz12003 · ‎11-22-2024

Hello, I have a sample dataset called 'Have'. In the 'Test_name' column, there are multiple pathogens with either 'IgG' or 'IgM' indicated after the pathogen name. I would like to create a new column called 'Sub_Analyte' that captures each pathogen and assigns the corresponding 'IgG' or 'IgM' suffix, as shown in the desired outcome in the second dataset 'Want'. Could you please advise on the best way to approach this? Thank you. data Test; length Test_Name $2000; infile datalines delimiter='#'; input Test_Name; datalines; Encephalitis Antibody Panel, CSF Includes: Herpes Simplex Virus (HSV) 1/2 (IgG) Type Antibody, CSF Lymphocytic Choriomeningitis (LCM) Virus (IgG, IgM) Antibody, IFA, CSF Measles (Rubeola) (IgG, IgM) Antibody Panel, IFA, CSF Mumps Antibody Panel, IFA, CSF Varicella-Zoster Virus (VZV) Antibodies (Total, IgM), ACIF/IFA, CSF West Nile Virus (WNV) Antibodies (IgG, IgM), CSF # Herpes Simplex Type 1 and Type 2 Glycoprotein G-Specific Antibodies, IgG by CIA# ; data Want; length Sub_Analyte $1000; infile datalines delimiter='#'; input Sub_Analyte; datalines; HSV_IgG, LCM_IgG, LCM_IgM, Measles_IgG,IgM, VZV_IgM, WNV_IgG, WNV_IgM# HSV_IgG# ;

ybz12003 · ‎09-05-2024

My Order_Test variable is a character, not numeric.

ybz12003 · ‎09-04-2024

Hello, In my sample test dataset, is there a way to get counts in Order_Test variable and assign descending date in the date variable? Proc report data=Test; column Health_Department epi_message_sender eor_spec_id Order_Test Updated_Date; define Health_Department / group ; define epi_message_sender / group ; define eor_spec_id / group ; define Order_Test / group ; define Updated_Date / group ; run;

ybz12003 · ‎09-03-2024

There are errors shown the logs. 62 %macro report_it(department_name); 63 ods excel options(sheet_name="&department_name."); 64 Proc report data=elc.Nrevss_251_CoV_noupdated; 65 column epi_message_sender Health_Department eor_spec_id Order_Test Updated_Date; 66 where Health_Department in ("&department_name."); 67 define epi_message_sender / group ; 68 define Health_Department / group ; 69 define eor_spec_id / group ; 70 define Order_Test / group ; 71 define Updated_Date / group ; 72 run; 73 %mend; 74 75 data _null_; 76 set work.CoV_251_NoUpdated_CNT end=last; 77 78 if COUNT < 10000; 79 80 if _n_=1 then do; 81 call execute('ods excel 81 ! file="pathway\Unmatch_&sysdate..xlsx";'); 82 end; 83 84 length command $1000.; 85 command = '%report_it(' || Health_Department || ');'; 86 call execute(command); 87 88 if end=last then do; 89 call execute('ods excel close;'); 90 end; 91 92 run; NOTE: Variable end is uninitialized. MLOGIC(REPORT_IT): Beginning execution. MLOGIC(REPORT_IT): Parameter DEPARTMENT_NAME has value Arizona Department of Health Services SYMBOLGEN: Macro variable DEPARTMENT_NAME resolves to Arizona Department of Health Services MPRINT(REPORT_IT): ods excel options(sheet_name="Arizona Department of Health Services"); MPRINT(REPORT_IT): Proc report data=elc.Nrevss_251_CoV_noupdated; MPRINT(REPORT_IT): column epi_message_sender Health_Department eor_spec_id Order_Test Updated_Date; SYMBOLGEN: Macro variable DEPARTMENT_NAME resolves to Arizona Department of Health Services MPRINT(REPORT_IT): where Health_Department in ("Arizona Department of Health Services"); MPRINT(REPORT_IT): define epi_message_sender / group ; MPRINT(REPORT_IT): define Health_Department / group ; MPRINT(REPORT_IT): define eor_spec_id / group ; MPRINT(REPORT_IT): define Order_Test / group ; MPRINT(REPORT_IT): define Updated_Date / group ; MPRINT(REPORT_IT): run; MLOGIC(REPORT_IT): Ending execution. MLOGIC(REPORT_IT): Beginning execution. MLOGIC(REPORT_IT): Parameter DEPARTMENT_NAME has value Arkansas Department of Health ERROR: More positional parameters found than defined. MLOGIC(REPORT_IT): Ending execution.

ybz12003 · ‎09-03-2024

Hello, I would like to create a do-loop list based on the "Health_Department" column of my sample test dataset below. This do-loop macro list will be used to output an Excel file in the next step using ODS. My goal is that the macro list replaces the "Ohio Dept of Health Laboratory" in the ODS steps, then does the list in the "Health department" of the Test dataset one by one. Could you please assist? Thanks. data test; Length Health_Department $200 COUNT 8; input Health_Department $ COUNT ; infile datalines delimiter='#'; datalines; Ohio Dept of Health Laboratory#7913247# South Dakota Department of Health#7913289# State of Florida Department of Health#8627568# Louisiana Department of Health#8627568# Montana Public Health Laboratory#9627588# ; run; ods excel file="Pathway\Unmatch_&tdate..xlsx"; ods excel options(sheet_name="Ohio Dept of Health Laboratory"); Proc report data=Sample; column Health_Department Order_Test Updated_Date; where Health_Department in ('Ohio Dept of Health Laboratory'); define Health_Department / group ; define Order_Test / group ; define Updated_Date / group ; run; ods excel close;

ybz12003 · ‎08-27-2024

Thanks for all the expert suggestions. I will try to modify the code to run my actual dataset.

ybz12003 · ‎08-27-2024

If there is any patient's demography information is updated, the date will be updated. However, too many variables are involved in the demography information. To simplify things, I want to check whether the Sample IDs or the time were changed. If changing, I will do further investigation. If not, I will ignor.

ybz12003 · ‎08-27-2024

I would like to create two separate datasets based on whether the time was updated. As shown in the sample dataset below, the caseids with the same state were unchanged, but the sample IDs will change if the date is updated. I want to identify which casids have been changed based on the date. The result datasets are shown below. data test; Length State $2 CaseID $9 SampleID 7; input State $ CaseID $ SampleID date: mmddyy10. ; format date mmddyy10.; infile datalines delimiter='#'; datalines; GA#EC1R00002#7913247#01/04/2024# GA#EC1R00002#7913289#01/06/2024# GA#EC1R00007#8627568#01/04/2024# GA#EC1R00007#8627568#01/04/2024# IN#EC1N00002#7913259#03/04/2024# IN#EC1N00002#7913389#03/09/2024# IN#EC1N00009#8827568#01/04/2023# IN#EC1N00009#8827588#01/24/2023# KS#EC1K00010#7945647#03/04/2022# KS#EC1K00010#7945647#03/04/2022# KS#EC1K00027#8827568#01/04/2023# KS#EC1K00027#8827588#01/24/2023# ; run; data Updated; Length State $2 CaseID $9 SampleID 7 change $1; input State $ CaseID $ SampleID date: mmddyy10. Change $; format date mmddyy10.; infile datalines delimiter='#'; datalines; GA#EC1R00002#7913247#01/04/2024#Y# GA#EC1R00002#7913289#01/06/2024#Y# IN#EC1N00002#7913259#03/04/2024#Y# IN#EC1N00002#7913389#03/09/2024#Y# IN#EC1N00009#8827568#01/04/2023#Y# IN#EC1N00009#8827588#01/24/2023#Y# KS#EC1K00027#8827568#01/04/2023#Y# KS#EC1K00027#8827588#01/24/2023#Y# ; run; data No_Updates; Length State $2 CaseID $9 SampleID 7 change $1; input State $ CaseID $ SampleID date: mmddyy10. change $1; format date mmddyy10.; infile datalines delimiter='#'; datalines; GA#EC1R00007#8627568#01/04/2024#N# GA#EC1R00007#8627568#01/04/2024#N# KS#EC1K00010#7945647#03/04/2022#N# KS#EC1K00010#7945647#03/04/2022#N# ; run;

ybz12003 · ‎08-22-2024

eot_test_ce1 is charater variable Changed to format eot_test_ce1 eot_test_ce1_.; Still did not work ERROR: The format EOT_TEST_CE1_ was not found or could not be loaded. MPRINT(AAA): format eot_test_ce1 eot_test_ce1_.; MPRINT(AAA): run;

ybz12003 · ‎08-22-2024

Hello, I have a format code before Proc Freq. However, an error message was shown in the log. Does the format only work in the numeric variables? Please help. proc format; value eot_test_ce1 '82160-3'='Adenovirus' '82161-1'='Cov HKU1' '82162-9'='CoV NL63' '82163-7'='CoV 229E'; run; Proc freq data=Sample.; table spec_id*eot_test_ce1 / nopercent norow nocol; format eot_test_ce1 eot_test_ce1.; run; ERROR: The format EOT_TEST_CE was not found or could not be loaded. MPRINT(AAA): format eot_test_ce1 eot_test_ce1.; MPRINT(AAA): run;

ybz12003 · ‎08-16-2024

Hello, I created an ID list below and ran a where statement afterward. However, the error was shown in the log. Please help. data CoV_HA_IDs; infile datalines dsd; input ID : $50. ; datalines; 1110, 1107, 1106, ; run; proc sql; select quote(strip(ID)) into : CoV_HA_ID_list separated by ',' from CoV_HA_IDs; quit; %put &CoV_HA_ID_list; %Macro aaa (Panel); proc tabulate data=NREVSS251_vw_&st.; class eor_spec_id eot_test_ce1 eot_test_ce2 erv_rslt_ce2; table eor_spec_id, eot_test_ce1*eot_test_ce2* (erv_rslt_ce2 all)* (N); where eor_spec_id in (&&Panel._ID_list.); run; %mend aaa; %aaa (CoV_HA); SYMBOLGEN: Macro variable ST resolves to UT MPRINT(AAA): proc tabulate data=NREVSS251_vw_UT; MPRINT(AAA): class eor_spec_id eot_test_ce1 eot_test_ce2 erv_rslt_ce2; MPRINT(AAA): table eor_spec_id, eot_test_ce1*eot_test_ce2* (erv_rslt_ce2 all)* (N); SYMBOLGEN: && resolves to &. SYMBOLGEN: Macro variable PANEL resolves to CoV_HA NOTE: Line generated by the macro variable "PANEL". 1 CoV_HA ------ 22 76 MPRINT(AAA): where eor_spec_id in (CoV_HA._ID_list.); ERROR: Syntax error while parsing WHERE clause. MPRINT(AAA): run; ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value, -. ERROR 76-322: Syntax error, statement will be ignored. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE TABULATE used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MLOGIC(AAA): Ending execution.

ybz12003 · ‎08-12-2024

Hello, Please help me to change the numeric format, DATETIME22.3 (12JAN2023:18:05:44.700) to mmddyy10 (10/12/2023). Thanks.

Online Status	Offline
Date Last Visited	‎12-18-2024 03:24 PM

Re: Title and Footnote could not be shown in the Output Excel file?

Title and Footnote could not be shown in the Output Excel file?

Re: Create a new 'Sub_Analyte' column?

Create a new 'Sub_Analyte' column?

Re: Count and assign descending date in Proc Report?

Count and assign descending date in Proc Report?

Re: How to create a do-loop list?

How to create a do-loop list?

Re: Creating a separated dataset for time changing?

Re: Creating a separated dataset for time changing?

Re: Create a new 'Sub_Analyte' column?

Re: Create a new 'Sub_Analyte' column?

Re: Title and Footnote could not be shown in the Output Excel file?

Re: How to create a do-loop list?

Re: How to create a do-loop list?

How to output two tabs with different names in Excel format?

Merge Multiple tables

Re: Why the command statement didn't work?

Re: Proc Univariate vs Proc Summary?

Text length question?

Re: Title and Footnote could not be shown in the Output Excel file?

Title and Footnote could not be shown in the Output Excel file?

Re: Create a new 'Sub_Analyte' column?

Create a new 'Sub_Analyte' column?

Re: Count and assign descending date in Proc Report?

Count and assign descending date in Proc Report?

Re: How to create a do-loop list?

How to create a do-loop list?

Re: Creating a separated dataset for time changing?

Re: Creating a separated dataset for time changing?

Creating a separated dataset for time changing?

Re: Proc Format didn't work?

Proc Format didn't work?

Where statement could not recognize Macro ID list?

Change DATETIME22.3 to mmddyy10. ?