Tom Tracker

Re: Assign variable values to time intervals

Tom — Thu, 07 May 2026 13:23:52 GMT

So assuming that by REF_DATE you mean the variable INDEX_DATE.

Let's also correct your example to more closely match your problem description (and represent the dates in YMD order to avoid confusing the number of the month with the day of the month).

data have;
  input ID :$20. (Index_date Date_event) (:yymmdd.) Event :$20. Flag :$20.;
  format Index_date Date_event yymmdd10.;
cards;
0001 2024-09-01 2024-10-11 Y 3M
0001 2024-09-01 2024-11-11 Y 3M
0001 2024-09-01 .          N . 
0001 2024-09-01 2025-01-15 Y 6M
0002 2016-09-11 2017-04-15 Y 9M
0003 2025-06-30 2025-02-09 Y . 
0003 2025-06-30 2025-12-12 Y 6M
0004 2024-12-03 .          N .
;

So we can use the INTCK() function to check the number of month boundaries crossed. I have chosen to use the continuous method instead of testing for when it crosses day one of the month. But you also might want to use a fixed number of days instead to avoid the fact that calendar months are not all the same length.

To convert from number of months to your 3,6,9,... values you can use the CEIL() function.

data want;
  set have;
  after = (date_event > index_date);
  if after then diff = intck('month',index_date,date_event,'c');
  diff3 = 3*ceil(diff/3);
run;

Results

Re: p106a02.sas export unsuccessful

Tom — Thu, 07 May 2026 12:56:32 GMT

Point the libref at the DIRECTORY that contains the FILE that is the SAS dataset.

libname pg1 "S:/workshop/EPG1V2/data/";

Re: Snowflake Queries

Tom — Thu, 07 May 2026 02:43:05 GMT

How many observations does that query return? You can test it in Snowflake or just wrap it into something like:

....
select * from connection to sf
(select count(*) as nobs from
 (... the query goes here ...) a
);

If you want to move a LOT of data then you might get better results using Snowflakes ability to generate a CSV file into some place like Amazon S3 bucket that you could then read with SAS code to make a dataset.

Are any of those variables defined as STRING in Snowflake? SAS datasets only have two types of variables. Fixed length character strings and floating point numbers. So when you have a character variable of unknown length in your Snowflake table SAS will use some default length for the variable when making the dataset. It might pick something very long such as SAS's maximum length of 32,767 bytes. That would make the processes of creating the SAS dataset take a very long time even if the transfer from Snowflake ran quickly.

Try just pulling over a handful of the observations (Snowflake's version of SQL should support something like a LIMIT clause to trim the number of observations returned by the query). You can then run PROC CONTENTS on the resulting SAS dataset and see how the variables are defined.

Re: p106a02.sas export unsuccessful

Tom — Wed, 06 May 2026 18:08:38 GMT

@ivarenho wrote:
Yes when i did try to run that in the past i got and still get two errors the first is library pg is not in valid format for access method random and error in lib name statement, so i would assume i need to go back and reformat the data?

You will need to show the log for the actual code you ran. Instead of taking pictures of the text just copy the text from the LOG and paste it into the pop-up window you get when you click on the Insert Code icon (looks like </>).

Most likely a message like that would be generated if you tried to point a libref at a FILE instead of a DIRECTORY. Here is an example using a dataset I have in my home directory on the SAS server.

 73         libname x "~/class.sas7bdat";
 ERROR: Library X is not in a valid format for access method RANDOM.
 ERROR: Error in the LIBNAME statement.
 74         libname x "~";
 NOTE: Libref X was successfully assigned as follows: 
       Engine:        V9 
       Physical Name: /home/tom.abernathy
 75         data _null_;
 76           set x.class;
 77           put (_all_) (=);
 78         run;
 
 Student Name=Alfred Sex=M Age=14 Height=69 Weight=112.5
 Student Name=Alice Sex=F Age=13 Height=56.5 Weight=84
 Student Name=Barbara Sex=F Age=13 Height=65.3 Weight=98
 Student Name=Carol Sex=F Age=14 Height=62.8 Weight=102.5
 Student Name=Henry Sex=M Age=14 Height=63.5 Weight=102.5
 Student Name=James Sex=M Age=12 Height=57.3 Weight=83
 Student Name=Jane Sex=F Age=12 Height=59.8 Weight=84.5
 Student Name=Janet Sex=F Age=15 Height=62.5 Weight=112.5
 Student Name=Jeffrey Sex=M Age=13 Height=62.5 Weight=84
 Student Name=John Sex=M Age=12 Height=59 Weight=99.5
 Student Name=Joyce Sex=F Age=11 Height=51.3 Weight=50.5
 Student Name=Judy Sex=F Age=14 Height=64.3 Weight=90
 Student Name=Louise Sex=F Age=12 Height=56.3 Weight=77
 Student Name=Mary Sex=F Age=15 Height=66.5 Weight=112
 Student Name=Philip Sex=M Age=16 Height=72 Weight=150
 Student Name=Robert Sex=M Age=12 Height=64.8 Weight=128
 Student Name=Ronald Sex=M Age=15 Height=67 Weight=133
 Student Name=Thomas Sex=M Age=11 Height=57.5 Weight=85
 Student Name=William Sex=M Age=15 Height=66.5 Weight=112
 NOTE: There were 19 observations read from the data set X.CLASS.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds

Re: p106a02.sas export unsuccessful

Tom — Wed, 06 May 2026 16:33:10 GMT

@ivarenho wrote:
In the autoexec. sas file i have libname pg1 "S/workshop/EPG1V2/"; but when I tried to change it to storm_final.sas7bdat I got an error I believe.

So that is not a valid path.

If your SAS session is running on a Windows machine then you need a colon after the S drive letter. S:/workshop/EPG1V2/

If your SAS session is running on a Unix machine then you need a fully qualified path. So the path needs to start with the root node. /S/workshop/EPG1V2/

The path used in the LIBNAME statement needs to point to a directory ("folder") and not to a file, so storm_final.sas7bdat should not appear in the LIBNAME statement that defines the PG1 libref.

In the photograph you posted you can see in the SAS/Studio Files and Folders tab that there is no sas7bdat files in that EPG1V2 folder. Perhaps you should be using the data subfolder? S:/workshop/EPG1V2/data/ Reread the instructions.

In SAS/Studio expand the data folder and check if the storm_final.sas7bdat file is there. If you can find the file then right click on the folder that contains it and ask for Properties. That should show you the exact string you need to use in the LIBNAME statement to point to that location.

If you cannot find the file then go back to the earlier steps that setup the files and make sure they worked. Perhaps when you ran those steps you also had the wrong path in the LIBNAME statement and so the dataset was never created.

Re: p106a02.sas export unsuccessful

Tom — Wed, 06 May 2026 15:46:47 GMT

The first ERROR message (the one in BLACK instead of RED) in your first picture is the one you need to fix.

That error means that the dataset you are trying to export does not exist. SAS could not find a dataset named STORM_FINAL in the libref named PG1.

Make sure you have defined the PG1 libref.

Make sure that the directory ("folder") that it points to contains a file named storm_final.sas7bdat.

Re: Coloring of words in SAS Viya/SAS Studio

Tom — Wed, 06 May 2026 14:58:03 GMT

That is not unique to VIYA. The SAS/Studio interface used in SAS ODA does the same thing. I have seen similar things in other editors WORD and VSCODE. I believe the idea is to let you know where else that text appears. That might be useful if you planned to do a global replace

In regular SAS/Studio you can find your cursor again if you press the left or right arrow key. That will move the cursor and remove the highlighting.

Re: Issue with proc format

Tom — Tue, 05 May 2026 21:07:29 GMT

Then why did you use WKS in your graph code as the XAXIS?

Shouldn't that be a variable that has the month (or the re-coded month)?

My guess is that you want to use the PANELBY feature of PROC SGPLOT to make multiply graphs on a single page where the individual graphs use different ranges of XAXIS values and different orders.

If that is NOT true then do what @Ksharp said and just run two PROC SGPLOT statements. One with the set of values that use the JAN -- DEC axis and another that uses the NOV--OCT axis.

Or you might want to use the ability of ODS itself to organize multiple "pages" into one page. Check out ODS LAYOUT.

If you want someone to help you work out the details then provide some example data, does not need to be the real data.

Re: Issue with proc format

Tom — Tue, 05 May 2026 18:42:10 GMT

Why do you change the definition of the format HARVEST in your program?

Originally it was defined with information on displaying the integers 1 to 12. Then later you replaced it with a version that only display the integers from 1 to 6. And it displays them in different ways. You should probably define two different formats in that case.

Remember that a FORMAT converts values into text. So it is used when you want to display the values in a particular way. If you want to display the values in another way then I would consider that a different format.

So perhaps something like this:

proc format;
value Months 
  1='Jan' 2='Feb' 3='Mar'  4='Apr'  5='May'  6='Jun' 
  7='Jul' 8='Aug' 9='Sep' 10='Oct' 11='Nov' 12='Dec'
;
value Months_I49L
  1="Nov" 2="Dec" 3="Jan" 4="Feb" 5="Mar" 6="May"
;
run;

Re: Issue with proc format

Tom — Tue, 05 May 2026 18:28:23 GMT

I still do not understand what your data is. Please share some data. And explain it.

So you have variable named HARVEST that appears to mean either a MONTH number or an ORDER number (or perhaps both?). Perhaps you should split that information into two separate variables?

You have one data step that maps HARVEST into a new variable named TAG. But it only does that for some of the observations. For the other observations you do not give TAG a value. Was TAG an existing variable already? How is it related to HARVEST.

And then later you replace the HARVEST/TAG variable in the graph with a third variable named WKS. What is the meaning of WKS? What types of values does it have? From your last picture it seems to have also had the HARVEST. format attached to it and so it looks like it has values of 1,3,4,5,6 and 8. What do those values mean?

Re: Snowflake Queries

Tom — Mon, 04 May 2026 20:16:09 GMT

@SASMom2 wrote:

Thank you for all your help! When I tried to run a Snowflake query that pull data from one table, it ran quickly but when I tried q query that pulls data from 5 tables in Snowflake, it kept running. Finally, I stopped it after 5 hours. When I run the same query directly in Snowflake, it runs in under a minute. So, I would definitely be using your method for queries that use one table but not for queries that use multiple tables. I was hoping that it would work for complicated queries as it would save a lot of manually going back and forth between Snowflake and SAS.

Thank you!

Without an example I can only guess what you did. But here are some possibilities:

You somehow asked SAS to execute the joins instead of giving the whole query to Snowflake to run. If you ended up doing that then SAS would probably try to pull the whole table over and then join them which could take a very long time. For example do NOT do something like this:

select * from work.mytable a left join snow.dbtable b 
  on a.id = b.id
;

Instead either upload the list SAS dataset into Snowflake then then perform the join completely in Snowflake.

The sizes of the result sets was vastly different between the two queries and so you are seeing how slow your connection to Snowflake is.

There are options you can turn on to have SAS write the SQL it sends to Snowflake into the SAS log so you can see exactly what SAS ended up asking to be run.

https://documentation.sas.com/doc/en/workbenchcdc/v_001/vwbacdata/n0sa0yux9sqf90n1ttzxhko2cit4.htm

You can then try running that code in Snowflake and time how long it takes.

Re: Snowflake Queries

Tom — Mon, 04 May 2026 04:25:21 GMT

Why? Are you trying to do code generation?

You can use SAS's macro language to do code generation.

%let fname = 'ABC';
%let lname = 'DEFG';
%let BMTH= '9999-12-31';
....
proc sql ;
....
where first_name=&fname
  and last_name=&lname
  and DOB = &BMTH
....

Re: Snowflake Queries

Tom — Mon, 04 May 2026 02:15:13 GMT

Remove the partial EXECUTE statement, that is what is missing the ().

It does not look like you are trying to execute anything so you don't need that statement.

Note that everything inside the () after the FROM CONNECTION TO xx clause has to be valid Snowflake SQL syntax.

Re: Snowflake Queries

Tom — Sun, 03 May 2026 19:11:58 GMT

You can use SAS code to execute queries in Snowflake. If you already have a LIBREF defined pointing to Snowflake database then that should be all you need.

You can use PROC SQL to run queries. First make the connection by using the CONNECT USING statement. Then you can use the EXECUTE statement to run a command that does not return any output. Or the FROM CONNECTION TO clause for one that does.

So let's assume you defined the libref SNOW pointing to Snowflake

proc sql;
connect using snow;
execute by snow (...some Snowflake command...);
create table want as 
select * from connection to snow
(... some Snowflake query ...)
;
quit;

You can use the front end tool Enterprise Guide to create and run the SAS code.

Re: Issue with proc format

Tom — Sat, 02 May 2026 17:38:26 GMT

I can make a graph that sort of looks like that using data like this:


data gem ;
  length Harvest $9 Variety $4 Wks 8 DTR 8 ;
  input Harvest -- DTR;
cards;
2020-2021 Haas 1 6.5
2020-2021 Haas 3 5.9
2020-2021 Haas 5 5.8
2020-2021 Haas 8 4.3
2020-2021 GEM  3 6.1
2020-2021 GEM  5 5.7
2020-2021 GEM  8 6.2
;

proc sgpanel data=GEM;
  panelby Harvest/ onepanel spacing=5 novarname;
  styleattrs DATACONTRASTCOLORS=(BLUE red);
  vline Wks
    /lineattrs=(pattern=solid thickness=2)
     response=DTR 
     group=Variety 
     grouporder=data 
     markers 
     stat=mean 
  ;
  rowaxis values=(0 to 20 by 2) ;
run;

Share your data (use a data step like my example above) so we can see what you are actually working with.

Re: Issue with proc format

Tom — Sat, 02 May 2026 17:26:01 GMT

Please be more detailed about what you are doing.

What are the variable names and types? What do the values mean? What role do they play in the graph you want to create?

Based on the code and the picture my GUESS is that in the dataset GEM you have a format attached to the numeric variable HARVEST that converts the values 1 to 'Nov', 3 to 'Jan' , ... 6 to 'May' and 8 to '8'. I make this guess because in the WHERE statement you limit the set of observations to the ones with those values of HARVEST. If you would like to make this graph using a different format for HARVEST then add a FORMAT statement to the graph. If you would like to simply remove the format and have the raw values displayed then do not include a format specification in the FORMAT statement.

format harvest ;

But that is just a guess.

Re: Issue with proc format

Tom — Sat, 02 May 2026 04:07:22 GMT

The format is applied to the values of the variable it is attached to. The values of other variables has no impact.

If you want the format to display the same values then make sure to code the VARIABLE with the same values.

For example you might test the values of the other variable when making decisions about how to assign the values.

data new;
  set one;
  where Variety in('I49L','Hass');
  if Variety='I49L' then do;
    if (Harvest eq 11) then tag=1;
    else if (Harvest eq 12) then tag=2;
    else if (Harvest eq 1) then tag=3;
    else if (Harvest eq 2) then tag=4;
    else if (Harvest eq 3) then tag=5;
    else if (Harvest eq 5) then tag=6;
    else tag=.;
  end;
  else do;
* Put code to create proper values of TAG for the 'Hass' variety here ;
  end;
run;

Re: Issue with proc format

Tom — Fri, 01 May 2026 23:30:32 GMT

Not sure what you mean.

Could you share a picture of the result and explain what part is different than what you want?

Could you share the data you use? Preferable as small as necessary to show the issue.

Re: How do I delete observations with dates occurring within another date for the same ID?

Tom — Fri, 01 May 2026 16:34:34 GMT

If you know which records are the real ones why not just keep those?

I think the test is just to eliminate those that fall completely within a longer stay.

data have;
  input ID (AdmitDate DischargeDate) (:mmddyy.);
  format AdmitDate DischargeDate yymmdd10.;
cards;
1 1/1/2025  2/4/2025
1 1/3/2025  1/3/2025
1 1/17/2025 1/17/2025
2 3/4/2025  3/4/2025
3 6/7/2025  6/9/2025
3 6/8/2025  6/8/2025
;

proc print;
run;

proc sql;
create table want as 
select * from have
except 
  (select a.* from have a 
   full join have b 
   on a.id=b.id
   and (a.AdmitDate ne b.AdmitDate or a.DischargeDate ne B.DischargeDate)
   where a.AdmitDate between b.AdmitDate and B.DischargeDate
   and a.DischargeDate between b.AdmitDate and B.DischargeDate
   )
;
quit;

proc print;
run;

But if this is claims data (or direct hospital records) then you probably cannot assume that there exists a single record with the FULL length of stay.

Re: How do I delete observations with dates occurring within another date for the same ID?

Tom — Fri, 01 May 2026 16:23:31 GMT

@Ksharp wrote:

Tom,
I just want to keep my code simple and readable.
I really don't care about the code is long or short .
I think the short code would bring side effect . The readable code is the most important thing.

The point was not readability, but performance. Your code made multiple passes through the data.