@FreelanceReinh Yes, you're right. No precision issue. Thanks for pointing that out. @SwissC Before writing the function I suggest you get data step code as clean as possible.  From what I understand there are two separate problems: 1. How to create a number with the desired significant figures? 2. How to display/print numbers?   I believe you solved 1 already.  Point 2 is a bit harder. There isn't a format that does such alignment around a decimal point. The only way I can think of is to create a string (character variable) with leading spaces. But that won't be enough because you have then also to use a non-proportional (monospaced) typeset like Courier AND you need to ensure that the leading blanks are protected - and how to do this will depend on the output destination (like &nbsp; and/or the <pre> tag for HTML).   Below sample code works for writing to the SAS log and should also work for listing output. Other output destinations will require additional work. data test; attrib char_value length=$32 informat=$32. value length=8 informat=best32. format=f32.31; input @1 char_value @1 value; datalines; 2981248.76403 1248.76403 248.76403 48.76403 8.76403 0.76403 0.076403 0.0076403 0.00076403 0.000076403 0.00007640387106801 -0.000076403 ; RUN; %let sig_figures=3; data rounded; set test; /** populate numerical variable with desired number of significan figures **/ format SigFig_Value best32.; sig_figures=&sig_figures; /* Determine the number of significant figures */ if value = 0 then SigFig_Value = 0; else SigFig_Value = round(value, 10**floor(log10(abs(value))-sig_figures+1)); /** create character variable with leading blanks so decimal point is always at the same string position **/ format char_SigFig_Value $char60.; char_SigFig_Value=put(SigFig_Value,best32. -l); char_SigFig_Value=cat(repeat(' ',18-length(scan(char_SigFig_Value,1,'.'))),char_SigFig_Value); /* write character variable to log where typeset is Courier */ put char_SigFig_Value $char60.; run; /* proc print data=rounded noobs; */ /* format char_SigFig_Value $char60.; */ /* run; */     ... View more

since the reloading table is not finished yet From how I understand your code you're loading the table first and the outage for your report consumers should be very short (drop/promote). Not sure if doing this within a single Proc Casutil as discussed here would make a significant difference.    Another possibility (can't test. Don't have Viya access anymore😥) could be just-in-time loading. Instead of refreshing the in-memory table you just refresh it's physical leg and then drop the table. The next user accessing the report will then trigger the load. No idea though what the impact is for report consumers that were already working with your report before the refresh.   And as another idea: You could have a load_dttm column in your CAS table and your report filters rows for max(load_dttm). You then append data to the existing CAS table as step 1 and then as step 2 delete the old rows.   And depending on your data you could also consider if there is a way that you can do a insert/update/delete instead of a full refresh ...but this would likely cause "wrong" data for the time where the delete already executed but the insert didn't finish yet. ... View more

Suggest you provide some sample data that also include a column with the desired result. This should remove a lot of ambiguity.  I don't fully understand what you're after. For example: How many significant figures (digits?) has 100.0? And how would you want to display it if you're after sample code "to print to 3 significant figures".   The last set of digits in your sample represents a number that SAS can't store with full precision. data test; attrib a_num length=8 format=best32. informat=best32. a_char length=$32 format=$32. informat=$32.; input @1 a_num @1 a_char; datalines; 2981248.76403 1248.76403 248.76403 48.76403 8.76403 0.76403 0.076403 0.0076403 0.00076403 0.000076403 0.00007640387106801 ; RUN; proc print data=test; run;   ... View more

If I understand right then you're after something like below. /* Create sample data */ data have; input Base :$20. Compare :$20.; datalines; 123456 123,456 ABC123 ABC123 HelloWorld Hello,World 789012 789012 987654 987,654 Test123 Test123 SASCode SAS,Code 987654 9876,54 ; run; data compare; set have; if 0 then do; /* define new vars with same length than orig vars */ base2 =base; compare2=compare; end; /* define regex to remove thousand separator */ prxid=prxparse('s/,(\d{3})/$1/'); base2=prxchange(prxid,-1,strip(Base)); compare2=prxchange(prxid,-1,strip(Compare)); diff_flg= (base2 ne compare2); run; proc print data=compare; run;   ... View more

@klroesner  From experience with SAS TS (but same applies also to many other support orgs): Whenever you get an email from them even if it's only some "we're still looking into it" info always send a reply back. The reason being: If you receive an email then the ticket status goes to something like "waiting for input from customer" which can lead to your case not being worked on. If you reply then the status goes back to something where TS needs to take the next action. ... View more

Migration from what to what?  Without really understanding what this is about it feels to me like a bad re-design done by someone with likely an object oriented background who doesn't know much about SAS and what SAS macros are. If I'm right then this will not only lead to a very significant increase in migration effort (with a risk of failure) but also to way more complicated code and a maintenance nightmare later on. ... View more

You could try and define the connection directly in Proc FedSQL   PROC FEDSQL <SAS-connection-option | CAS-connection-option> <processing-options>;     But... like you I'd expect the libname to also work for Proc FedSQL. I suggest you raise this with SAS Tech Support. If you do so please update the discussion here as well so we can learn something. ... View more

@freshstarter wrote: Attached here the input file.   The date in the excel file is 02/08/2023 ( which is 02nd Aug 2023 ) . Our final result expected to be the same in another csv file. This is how our workflow created @freshstarter  The date in the Excel you attached is 4th of July and not as you write 02/08/2023   Proc Import will use the Excel cell Format Code to convert the cell value into a SAS date. SAS stores dates in a numerical variable as the count of days since 01Jan1960. SAS date formats make such counts human readable as date strings.   Proc Contents will tell you what format Proc Import attached to the variable.   Proc Export will use this format to write strings to a .csv (which is just a text file). For example if the format is date9. then the SAS count of days since 01Jan1960 will get written as a date string of the form ddmonyyyy   I've attached an Excel to demonstrate what I'm talking about.   Even though the start dates in the two data rows look the same they will create different SAS date values when imported into SAS. The reason is that they've got different cell formats (format code).    You can see for yourself if running below code using the attached Excel. %let root_path=/home/&sysuserid; PROC IMPORT DATAFILE="&root_path/temp/input_excelfile_gb_us.xlsx" DBMS=XLSX OUT=WORK.excel_gb_us replace; GETNAMES=YES; RUN; proc sql; select format_cells_language ,format_cells_category ,format_cells_format ,format_cells_formatCode ,start_date format=best32. label='start_date format=best32.' ,start_date format=date9. label='start_date format=date9' ,start_date format=ddmmyy10. label='start_date format=ddmmyy10.' ,start_date format=mmddyy10. label='start_date format=mmddyy10.' from WORK.excel_gb_us ; quit; The highlighted column above is showing how SAS actually stores the date values (just count of days since 1/1/1960 as a number). The other columns then show how SAS prints these numbers using some of the available date formats. And it's the same when writing to a .csv. It's just the format that "decides" how the values will get written.   And last but not least: Similar to SAS also Excel stores dates as the count of days since a start date. With Excel day one is 01Jan1900. The Excel cell format then "decides" how the date displays in the spreadsheet. SAS needs only to know that the cell value is a date and then it's a simple formula to convert the Excel day count to the SAS day count (...with some minor challenge for 1900 where Microsoft wrongly assumed it's a leap year).               ... View more

@freshstarter In your Excel are these cells with date defined as character or date or something else. What would help: Create a test Excel with just a date column and a few dates that creates this issue in your environment Share with us this Excel here as an attachment and tell us what the issue is you observe and what the expected result should be Please share with us the exact Excel you've used for your testing Such an Excel would allow us to do some testing to see if we can replicate the issue you describe.     ... View more

It sounds like there has been a recent change to your Prod environment.  Have you already tried what happens if you set at the beginning of your job below two options. options datestyle=dmy locale=en_gb; ... View more

Using SAS you can replace a sheet in an existing Excel workbook using Proc Export. Except for using DDE (which is obsolete since many years) you can't change cell values in an existing sheet. For changing selected cell values I'd be using Python eventually calling the script out of SAS. The reason for Python is it's degree of integration into SAS. The SAS Viya Studio version even comes with a built-in Python editor. ... View more

@zouinenoah  Below your Proc Format code with the required fixes applied (no semicolon after the value statement and one repeated group removed). I'm not sure though what the purpose of format  $AttendingSpecialty  could be given all values map into the same label. proc format; value $CompanyCode (multilabel notsorted) "900"="Aurora Medical Center Grafton" "902"="Aurora St. Luke's Medical Center" "903"="Aurora St. Luke's South Shore" "904"="Aurora Sinai Medical Center" "905"="Aurora West Allis Memorial" "906"="Aurora Medical Center Washington County" "907"="Aurora Memorial Hospital of Burlington" "908"="Aurora Medical Center Kenosha" "909"="Aurora Lakeland Medical Center" "910"="Aurora Sheboygan Memorial Medical Center" "911"="Aurora BayCare Medical Center" "912"="Aurora Medical Center Oshkosh" "913"="Aurora Medical Center Summit" "914"="Aurora Medical Center Manitowoc County" "916"="Aurora Bay Area" ; value $AttendingSpecialty "Hospitalist"='Hospitalist' " ", "Addiction Medicine", "Advanced Heart Failure and Transplant Cardiology", "Anesthesiology", "Bariatric Surgery", "Cardiac Electrophysiology", "Cardiology", "Cardiothoracic Anesthesiology", "Cardiothoracic Surgery", "Cardiovascular & Thoracic Surgery", "Cardiovascular Disease", "Child Psychiatry", "Child/Adolescent Psychiatry", "Colorectal Surgery", "Critical Care", "Critical Care Medicine", "Electrophysiology", "Emergency Medicine", "Family Medicine", "Family Practice", "Gastroenterology", "General & Vascular Surgery", "General Medicine", "General Surgery", "General Surgey", "Geriatric Medicine", "Gynecologic Oncology", "Hematology & Oncology", "Hematology/Oncology", "Internal Medicine", "Interventional Cardiology", "Maternal/Fetal Medicine", "Neonatal & Perinatal Medicine", "Neonatalogy", "Neonatology", "Neurological Surgery", "Neurology", "Neuroradiology", "Neurosurgery", "OB/GYN", "Obstetrics", "Obstetrics & Gynecology", "Obstetrics/Gynecology", "Oncology", "Orthopaedic Surgery", "Orthopedic Surgery", "Orthopedics", "Otolaryngology", "Otorhinolaryngology", "Pediatric", "Pediatric Critical Care", "Pediatric Emergency Medicine", "Pediatric Gastroenterology", "Pediatric General Surgery", "Pediatric Hematology/Oncology", "Pediatric Medicine", "Pediatric Orthopedic Surgery", "Pediatric Palliative Medicine", "Pediatric Pulmonology", "Pediatric Urology", "Pediatrics", "Physical Medicine & Rehab", "Physical Medicine & Rehabilitation", "Plastic Surgery", "Plastic and Reconstructive Surgery", "Plastic/Reconstructive Surgery", "Podiatry", "Psychiatry", "Pulmonary Medicine", "Radiation Oncology", "Sleep Medicine", "Surgery", "Surgical Critical Care", "Thoracic & CV Surgery", "Thoracic Surgery", "Thoracic and Cardiac Surgery", "Trauma Surgery", "Urogynecology", "Urology", "Vascular", "Vascular & Interventional Radiology", "Vascular Surgery", "eICU physician", "eICU-Lead" ='All Specialties' ; run; ... View more

To add to what @ballardw wrote: It looks like you need to buy these descriptions from the AMA. Based on this Reddit discussion it appears there are also work-arounds to get to these codes.  HCPCS Level I \ CPT Codes full list ... View more