You're creating a character informat. The docs state: "The INVALUE, PICTURE, and VALUE statements accept numeric values on the left side of the equal sign. In character informats, numeric ranges are treated as character strings." https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n03qskwoints2an1ispy57plwrn9.htm   So even though you've typed what looks like a number, it is treated in the informat as a character string.   If you know you always have character strings that represent integers in the range 0-100, you might be able to hack together something workable like:   proc format; invalue $Grades(upcase just) 90 - 99,100 = 'A' 80 - 89 = 'B' 70 - 79 = 'C' 60 - 69 = 'D' 0,1,6,7,8,9,10,11 - 59 = 'F' other = _same_ ; run; data GradesExample; input Grade : $Grades3. @@; datalines; 100 56 a 79 94 84 C100 8 ; run; But I think life will be easier if you just read the grades into a numeric variable, and use a format to group the numeric ranges into the letter grades.   ... View more

I suppose it's possible that PROC UCM is using a different dataset to make that plot than the output dataset you are using. Maybe you could try running the PROC UCM  step inside an ODS TRACE ON sandwich to see if there are any other datasets generated which might have the data in the format you want.  Often the datasets you can get from OUTPUT statements / OUT options are different than the "internal" datasets that are exposed via ODS.   PROC UCM should be using a GTL template to make the plot.  I guess it's possible there is some GTL magic which allows you to make this sort of partial bland plot, but I don't remember it.  (I don't use GTL much anymore, I find the SG procs can do everything I need). You can use ODS TRACE ON to see which graph template PROC UCM is using, then you can use PROC TEMPLATE to view the template: proc template; source Base.Freq.Graphics.OneWayFreqDotPlot; *this is for FREQ, replace with template used by PROC UCM to make the plot; run; ... View more

I think easiest approach would be to add a DATA step to delete the LCL / UCL values before the refline, as you suggest.  I don't know PROC UCM, I suppose it's worth checking if there is a PROC UCM option that would only calculate the LCL / UCL for the forecast, not the actual.     But for the SGPLOT, I don't think there is a way to say make this BAND: band x=Month lower=LCL upper=UCL / legendlabel="95% Confidence Limits" name="CL"; but only for Month values >= Jan 2020. I suppose if you really wanted to you could try to add some sort of dynamic formatting to make the band color transparent before that date.  But I think it would be easier and clearer code if you go with the DATA step approach. ... View more

@Ronein wrote: Thanks. Regarding question 3- let's talk about customer 1. There are 8 rows for customer 1. Are there 8 iterations for customer 1? Yes, the DO UNTIL loop will iterate 8 times for customer 1.   As I understand in each iteration there is set of one row and the next row delete the previous row and so on so at the end only the last row left? Is it true? This is close to correct.  The DATA step uses a program data vector (PDV) to do its work.  You can think of the PDV as a single row (vector), which the DATA step uses to hold data it is working on.  Every DATA step has one PDV, and it is only one row, and it has all the variables that exist in the DATA step.   When the SET statement executes, it reads one record from the dataset into the PDV.  Then next time the SET statement executes, it reads the next record from the dataset into the PDV.  Nothing is deleted from the PDV.  But each time data is read into the PDV, it overwrites any data that was there before.   How can I understand and see the process? Is it possible to see what sas do in each step ?( I mean to see the data in eqxh step)? You can use PUT statements to write the values in the PDV to the log.  This lets you see the values of variables in the PDV at any point.  For example, you could update your code to be:     data want; pop7=0; failure=0; do until(last.CustID); set have; by CustID; if (ddate='01NOV2022'd and pop=7) then POP7=1; if ddate in ('01OCT2022'd,'01SEP2022'd ,'01AUG2022'd,'01JUL2022'd,'01JUN2022'd) and Ind_Fail=1 then FAILURE=1; put "Bottom of DO until loop " (_N_ CustID ddate pop Ind_Fail pop7 failure last.CustID)(=) ; end; put / "Bottom of DATA step loop. This record will be output: " / (_N_ CustID ddate pop Ind_Fail pop7 failure last.CustID)(=) / ; run;     Regarding question 1- why is the purpose of initiate values 0 for 2 variables? Is outcome be different if we don't do it? Yes, the outcome will be different.  Try commenting out statements, then run the code, and look at values the PUT statement writes to the log.   For more reading, I would recommend: https://support.sas.com/resources/papers/proceedings09/038-2009.pdf . ... View more

My setup is similar, except I'm running 9.4M7, and 64-bit SAS on Windows, so I'm using the 64-bit ODBC driver from snowflake. @AndrewZ wrote: Yes, I checked this is SAS Unicode, then double checked, triple checked, and then checked a few more times.   Here is a screenshot       ... View more

Hi @AhmedAl_Attar .  Understood that encoding data in UTF-8 will require longer variables, but I think SAS should handle that gracefully, in a SAS session with UTF-8 encoding.   More to the point, the issue does not seem to be that variables are being truncated.  Instead it looks like the SAS dataset which has a dataset attribute stating it is UTF-8 encoded, has values that are actually iso-8859 encoded.  Please see this post: https://communities.sas.com/t5/SAS-Programming/Reading-Unicode-from-Snowflake-into-SAS-via-ODBC/m-p/942383/highlight/true#M369527.  I think that post shows that the O-umlaut character is encoded incorrectly in the SAS dataset.  But the values are not being truncated. ... View more

Thanks Andrew.  I feel like if there were a problem with the ODBC driver Snowflake provides, there would be LOTS of people complaining over at stack overflow etc.  When I search for this issue, I don't see many complaints.  And my guess is that among SAS folks reading from Snowflake, most folks have SAS/ACCESS to Snowflake, so might not have been tripped up by this issue.  I feel like it's probably a SAS problem.  They probably won't be able to fix it any time soon, but maybe some brilliant person in tech support will come up with an interesting workaround. ... View more

A month later, I'm back to playing with this.   No big insights.   I start with a value: A.Ö. Krankenhaus, stored in snowflake.   In a UTF-8 SAS session when I pull that value from snowflake, it shows that O with umlaut is encoded as "D6"x (using pictures instead of text cuz want to avoid the browser decoding stuff):   The SAS dataset is tagged as UTF-8 encoded, but "D6"x is not the proper UTF-8 encoding for O with umlaut.  As per https://www.ascii-code.com/character/%C3%96 it should be encoded as "C396"x.  "D6"x is the ISO-8859-1 encoding. So the above does not show O with umlaut, it shows a box. If I use your PROC DATASETS "trick" to tell SAS that the dataset is not encoded UTF-8, it's actually encoded iso-8859-1: proc datasets library=work noprint; modify krak2 / correctencoding='iso-8859-1'; quit; Then everything works: That shows that CEDA was used.  So I guess since I'm in a UTF-8 SAS session and SAS now knows the data are stored as iso-8859-1, it is translating the "D6"x value (which is the iso-8859-1 encoding for O umlaut) into "C396"x which is the UTF-8 encoding. So I agree with your conclusion, SAS is somehow receiving iso-8859-1 data from the snowflake ODBC driver, even though it should be sending UTF-8. I then used the same ODBC driver to read the data into PowerBI, and everything seems to have worked fine.  Which makes me wonder if the problem might be on the SAS side. ... View more

I would suggest using PROC REPORT, or maybe even just PROC PRINT to generate the report.  It gives you control over font size, column widths, etc.  You can still use PROC SQL to calculate the values, then use PROC REPORT to print them out.     @JaneEslinger has a bunch of great papers on ODS PowerPoint, including one specifically on the topic of how to make content fit a slide: https://www.lexjansen.com/pharmasug/2018/DV/PharmaSUG-2018-DV01.pdf   ... View more

Perhaps this usage note will help.  Looks like an open issue: https://support.sas.com/kb/61/325.html. ... View more

As background: I used to say I was a programmer who only knew one language (SAS), until it was repeatedly pointed out to me how many languages there are in SAS. I've always been vaguely interested in these lists, as in enough to read the occasional LinkedIn or blog post, but not enough to worry about it too much.   I don't find the general programing language lists very useful.  As somebody who uses SAS for data management and analytics, it never worried me that there were many more .NET or javascript programmers out there than SAS programmers.  There are more doctors, car mechanics, and restaurant workers also, and the comparison feels almost as irrelevant.   I do find the comparison between SAS, R, and Python interesting, and keep 1/4 an eye on those trends.   Not surprisingly, Python is well in the lead in terms of numbers, but probably 90% (?) of Python is not for analytics or data management, it's a general programming language.   But still, I think the relative growth/trends of R, SAS, and Python are interesting.  And I think focusing on the job market is probably the best metric available.   I was actually surprised how much lower R scored than SAS.  But as I think about it, R feels like a more limited language than SAS. I feel like R (because it came from S/S+) is basically still a statistical language.  And while it has AI/ML packages, python is way ahead in AI/ML.  And I'm not sure I'd want to serious do data management  / data engineering in R.   Within the field of analytics, I think one of the reasons SAS shines is that it has packages/solutions that do everything you need: Data management/Data engineering Traditional stats/biostats AI/ML   Whereas R and Python probably only do one or two of those well. That said, I'm a big fan of SAS's strategy to embrace open source, both calling R / Python / etc. from SAS, and allowing R/Python/etc to call SAS analytics.   When I join occasional R community calls, in the past it felt like R wanted to be a "SAS-killer".  And probably some of that community still does.  But now on those calls I feel like the R community is sometimes super-defensive about the rise of Python.  I see a lot of python-bashing  in the chat, as if they're worried that R is no longer the revolutionary open-source language, and may itself be over-thrown.  It was super-interesting that the company R-studio changed their name to Posit and embraced Python.  We'll see how that goes.    Meanwhile at SAS user group conferences, it's been great to see the growth of R and Python.  I feel like not just SAS Inc, but also the SAS community, has benefitted from truly embracing open-source languages.   ... View more

Please post the log.  Often in the log there are multiple error messages which explain the problem, or even one one error messages and one note or warning.     For example, when I run your code, because I don't have the csv files, I get the following in the log: 1 PROC IMPORT datafile = "C:\Users\foldername\Pulmonologists_Hung.csv" 2 DBMS = CSV 3 out = work.pulmon_hung; 4 guessingrows=MAX; 5 getnames=yes; 6 run; ERROR: Physical file does not exist, C:\Users\foldername\Pulmonologists_Hung.csv. ERROR: Import unsuccessful. See SAS Log for details. NOTE: The SAS System stopped processing this step because of errors. So the first error message explains the problem. ... View more

@Nipun22 wrote: you mean folder should also be different though libraries are different already? Yes, if you think in terms of the files, your current code is trying to copy the file /home/u60697506/sasuser.v94/Permanants Libs/a.sas7bdat to: /home/u60697506/sasuser.v94/Permanants Libs/a.sas7bdat and SAS is recognizing that you're trying to copy a file to the same place the file is already located, which doesn't make sense. ... View more

That's a weird message.  What does the log show?   What happens if you run the code without the create table clause, i.e.: PROC SQL; SELECT DISTINCT t1.Store_ID, t1.Customer_ID, t1.CLUSTER_ID FROM adm.Jumb_ah t1 ORDER BY t1.CLUSTER_ID; QUIT; Is the source data (adm.Jumbh_ah) a SAS dataset, or some relational database?     In the part of the screen shot that you blacked out, what data set is the message referring to?  Is it adm.Jumb_ah or work.Query_For_ah or something else?   Could adm.Jumb_ah be a database view with integrity constraints, rather than a table?  And when you query from adm.Jumb_ah one of the integrity constraints fails?     ... View more

You have SAS installed on the Windows machine?   Using Windows scheduler, I use "Create Task" Then for action I select "Start a program"   The program points to the sas executable, e.g.: "C:\Program Files\SASHome\SASFoundation\9.4\sas.exe"   And then I add the path to my .sas file as an option  -sysin "C:\MyDir\MyProgram.sas"   One the main task page, there are options for storing credentials, etc you might have to play with. But as Paige said, please explain what you have tried, even showing screen shots of the task you have tried could hlep.  And then show what happens when you try to run the task manually.  It may show helpful error messages if the Windows scheduler can't start the job.       ... View more