Tom Tracker

Re: SAS Programming 1: Essentials Lesson 6 Activity p106a02.sas

Tom — Wed, 13 May 2026 17:50:21 GMT

My suspicion is that the author of the EFI tool that PROC IMPORT/EXPORT uses has retired and SAS support is afraid to touch it and remove that useless NOTE for fear of breaking something.

Re: SAS to Snowflake data load using Key Pair

Tom — Tue, 12 May 2026 23:37:05 GMT

Don't know about ROLE, but why not try using the SCHEMA= dataset option.

data snow.mytable(schema="myschema");
  set perm.mydataset;
run;

Re: Transposing a long dataset to wide with multiple variable types

Tom — Tue, 12 May 2026 15:46:01 GMT

Remember to use the Insert Code or Insert SAS Code icons on the forum editor when saying text blocks or code blocks. That will prevent the text from being flowed as paragraphs.

Also when sharing data as text make sure that you have clear delimiters, preferably ones that are visible to humans.

Also when sharing a dataset provide a working data step that can recreate the dataset so that other's can make a copy of your data to program from.

data have;
  infile cards dsd dlm='|' truncover firstobs=2;
  input SID :$9. (FirstName MiddleName LastName) (:$20.)
   DOB :$10. FormName :$32. FormDateTime :anydtdtm. 
   Question :$500. QuestionPosition QuestionType :$30. Response $200.
  ;
  format FormDateTime datetime19.;
cards4;
SID|FirstName|MiddleName|LastName|DOB|FormName|FormDateTime|Question|QuestionPosition|QuestionType|Response|$
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|This document serves as a guide for care team members to establish, track, and evaluate treatment goals for clients. It aims to facilitate client-centered care and promote collaboration among multidisciplinary team members. Please note that this document should be re-evaluated post-STAIR/post-WET, diagnoses dependent. Please note if a client is on unit longer than a month this document should be reviewed, re-evaluated, and if necessary updated.|1|Information
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Program StartDate|2|Date|8/1/2025
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Language conducted in|3|Text - Short|English
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Time In|4|Text - Short|1:00 PM
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Time Out|5|Text - Short|1:30 PM
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Date of Treatment Plan Completion|6|Date|8/15/2025
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Current Revision Date|7|Date|8/15/2025
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Plan Version #|8|Text - Short|1
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Duration (rounded to nearest 15-minute interval)|9|Integer (Number)|30
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Target Date for Completion|33|Date|10/31/2025
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Mental Health Treatment Goals|34|Section header
999994|Jeff||T-Bars|1/1/2000|Project|8/15/2025 13:28|Goal|35|Text|Client will actively participate
;;;;

So the first thing you need to do is figure out what variables (questions) appear on the form. If you do not have that available directly from the survey metadata then perhaps you can just summarize what you see in the data file.

proc sql;
 create table variables as
 select cats('VAR',put(QuestionPosition,z2.)) as VarName
      , count(*) as nobs
      , QuestionPosition
      , QuestionType
      , Question
 from have
 group by 1,3,4,5
 ;
run;

Result

That should provide most of what you need to create a program to read the actual responses into those variables. But you are missing some information. SAS stores character variables as FIXED length. So you need some way to decide how long to make 5 text variables. Again if they did not provide any metadata that would describe the variables you could try to GUESS a length based on the lengths of the responses in the file.

proc sql;
 create table variables as
 select cats('VAR',put(QuestionPosition,z2.)) as VarName
      , count(*) as nobs
      , max(length(response)) as Length
      , QuestionPosition
      , QuestionType
      , Question
 from have
 group by Varname,QuestionPosition,QuestionType,Question
 ;
run;

Also some of those "questions" look more like metadata and not actual user responses. So I doubt that you need to carry Questions number 1 and 34 into the output dataset. In fact question 34 looks like perhaps it should be used to decide what DATASET to create so that you have all of the questions from the same section of the survey in the same SAS dataset.

So for this example perhaps you want to generate a data step like this:

data want;
  do until(last.FormDateTime);
    set have;
    by SID -- FormDateTime ;
    
LENGTH    
 VAR02 8
 VAR03 $8
 VAR04 $8 
 VAR05 $8
 VAR06 8
 VAR07 8
 VAR08 $1
 VAR09 8
 VAR33 8
 VAR35 $40
;
LABEL    
 VAR02 ='Program StartDate'
 VAR03 ='Language conducted in'
 VAR04 ='Time In'
 VAR05 ='Time Out'
 VAR06 ='Date of Treatment Plan Completion'
 VAR07 ='Current Revision Date'
 VAR08 ='Plan Version #'
 VAR09 ='Duration (rounded to nearest 15-minute interval)'
 VAR33 ='Target Date for Completion'
 VAR35 ='Goal'
;
 FORMAT
 VAR02 date9.
 VAR06 date9.
 VAR07 date9.
 VAR33 date9.
;  
 SELECT (QuestionPosition);
   WHEN (2) var02 = input(response,mmddyy10.);
   WHEN (3) var03 = response;
   WHEN (4) var04 = response;
   WHEN (5) var05 = response;
   WHEN (6) var06 = input(response,mmddyy10.);
   WHEN (7) var07 = input(response,mmddyy10.);
   WHEN (8) var08 = response;
   WHEN (9) var09 = input(response,32.);
   WHEN (33) var33 = input(response,mmddyy10.);
   WHEN (35) var35 = response;
   OTHERWISE ;
 END;
   end;
   drop question -- response;
run;

Let's print the results (using the labels).

proc print label data=want;
 var SID var: ;
run;

Result

You then just need to work out how to generate that data step from the metadata (or the VARIABLES dataset you generated from the input data). That is not very hard since you can use a data step to write the code to a text file and then just %INCLUDE it to have SAS run it.

Re: Transposing a long dataset to wide with multiple variable types

Tom — Tue, 12 May 2026 01:28:34 GMT

Since you are using this BY statement in your PROC TRANPOSE

by SID FormDateTime FirstName MiddleName LastName DOB;

To get duplicate observations you would need to have changes in one of those variables. Are you sure that FORMDATETIME is the same of all observations you want to combine? What about the other 5 varaibles?

Or are you just talking about the fact that you generated TWO datasets, but did not include a step to combine them?

data want;
  merge wide_num wide_char;
  by SID FormDateTime FirstName MiddleName LastName DOB;
run;

Also you will need to apply the FORMAT to the variables AFTEr the PROC TRANSPOSE step.

For example both DATE and TIME variables are numeric, so I would assume you put them into the WIDE_NUM dataset. But if you do not attach a FORMAT to the resulting variable(s) then the values will be hard for humans to interpret.

Re: Transposing a long dataset to wide with multiple variable types

Tom — Tue, 12 May 2026 00:45:41 GMT

It looks like you currently have the data stored in a single character variable named RESPONSE.

You appear to also have a variableTARGET_TYPE that appears to indicates what TYPE of variable needs to be created to store this particular RESPONSE.

What variable has the NAME that you want to use for the new variable?

What are the variables that uniquely identify the set of observations you want to transpose into one?

Do you need code that works for this particular dataset, with its specific set of variables?

If so then just write some code that that reads in all of the observations for the output observation in one iteration of the data step.

So something like:

data want;
  do until (last.id);
    set have;
    by id;
    if NAME='DATE' then date=input(left(response),anydtdte.);
    else if NAME='AGE' then age=input(left(response),32.);
    ...
  end;
  drop NAME TARGET_TYPE RESPONSE ;
run;

Or do you want a program that could convert ANY dataset? If so then select the unique combinations of NAME and TARGET_TYPE from the dataset and use that to generate a program like the one above.

For a more detailed answer provide a small example input dataset (as a working DATA step). Also provide a data step that creates the dataset you want to get from that example input.

Re: Demystifying Macro Masking Functions Q&A, Slides, and On-Demand Recording

Tom — Fri, 08 May 2026 21:37:16 GMT

Thanks.

Minor typo on page 9 of the PDF file.

Macro variable names often contain special characters, ...

I assume you meant say variable VALUES instead of variable NAMES. Macro variable names could never contain any special characters because they follow normal SAS naming conventions.

And this question in the Q&A is simple if you just take a minute to think about it.

Why do you not need & before list in the final example that uses %superq?

Answer:

I don’t really know why …. Good question. I’ve just accepted this as a code developer’s whim.

If you added an & then that would mean you want to pass the VALUE of the macro variable to %SUPERQ(). But SUPERQ wants the NAME of the macro variable. If you tried to pass the value and it contains one of the special characters then you would need to pre-quote the value before %SUPERQ() could see. And if you did want to quote the value you could just use %BQUOTE().

Where you might use & when calling %SUPERQ() is when you want to generate the NAME at execution time. So perhaps you have a macro variable with the name of another macro variable. This code will add macro quoting to the value of the NAME1 macro variable.

%let myvar=name1;
%let &myvar = %superq(&myvar);

Or perhaps you have series a macro variables with the same basename and different suffixes.

data _null_;
  set sashelp.class;
  call symputx(cats('name',_n_),name);
run;
%let basename=name;
%let suffix=1;
%let &basename.&suffix = %superq(&basename.&suffix);

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.