Tom Tracker

Re: Does hash join not honor the memsize limitation?

Tom — Wed, 16 Sep 2026 19:40:21 GMT

That does not look like valid syntax for invoking SAS from the command line.

You have two filenames specified after the -sysin option.

SInce the second filename ends in .cfg perhaps you forget to include the -config option?

Are you sure your hash object will actually fit in 8G?

Re: ReWriting a CSV to remove end of file reference

Tom — Wed, 16 Sep 2026 01:51:54 GMT

I assume you meant END OF LINE and not END OF FILE. If you really meant END OF FILE then look at the IGNOREDOSEOF option of the INFILE statement.

Simple answer is to just use an existing program instead.

https://github.com/sasutils/macros/blob/master/replace_crlf.sas

%let repA=' ';
%let repD=' ';
%let dsnnme="\\path\export.csv";
%let dsnnme1="\\path\export2.csv";
%replace_crlf
(infile=&dsnnme   /* Fileref or quoted physical name of input file */
,outfile=&dsnnme1  /* Fileref or quoted physical name of output file */
,cr=&repD /* Replacement string for carriage return */
,lf=&repA  /* Replacement string for linefeed */
);

The reason your program did not work is because you only wrote the replacement characters. Add an ELSE statement so that other values of A are written.

if open and (a='0D'x) then put &repD;
else if open and (a='0A'x) then put &repA;
else put a $char1. ;

Re: Column shift due to unbalanced quotes

Tom — Sat, 12 Sep 2026 20:35:49 GMT

It is hard to write a program for all possible invalidly formatted delimited files.

For ones what are just clearly messed up the details of how they are messed up matter. For example why are the quotes unbalanced? Did they not add quotes around the values that contain quotes? Did they not double up the embedded quotes so that they could be distinguished from the quotes that surround a value?

But there are a number of delimited files that are well defined, but just don't follow SAS's rules. For those if you can recognize them you can adjust for them.

For example SAS does not support end-of-line indicators that are embedded inside a value, even if the value is quoted. For those you need to pre-process the file to remove/replace the end-of-line indicators. Here is a macro that does that. %replace_crlf()

It works by counted the number of quotes to tell whether the end of line characters (carriage return or linefeed) appear inside quotes and then replaces those characters.

Also SAS does not handle files that use an escape character (typically a backslash) to allow delimiters or quotes inside values. If such values already have quotes around them then you can just replace them. So replace \" with "" and \, with , and then SAS will be able to parse the file properly. If there are not quotes around the values then replace the \" and \, with some other strings that do not appear in your file. You can then read the resulting file normally. Just remember to convert your replacement strings back to the intended value.

For more specific help with your file post examples of the lines that are poorly formed.

Re: Column shift due to unbalanced quotes

Tom — Sat, 12 Sep 2026 03:14:36 GMT

The SAS data step has many features to help you debug this type of file.

You can use the LIST statement to dump the lines from the file to the SAS log so you can SEE what you are dealing with.

You can reference the _INFILE_ automatic variable to allow you to run test against the current line (assuming the lines are less than 32K bytes long). So you could for example use the COUNTW() function to see how many values are on a given line and make sure that each line has the same number of values.

One simple idea to start with would be to use this %CSV2DS() macro which has a number of enhancements over the defaults of PROC IMPORT. (See this thread https://communities.sas.com/t5/SAS-Programming/Reading-delimited-text-files/m-p/775298 ) One of the things that macro will do is setup a view that has each value as a separate observation.

data _values_(keep=row varnum length short) / view=_values_;
...

It uses this to gather the statistics it uses to base its guesses about how to read the variable in and whether to attach a format.

One enhancement that I asked SAS for years (perhaps decades) ago is support for Unix style escape characters in delimited files. They never did anything about it but now that new versions of SAS allow you to use PROC PYTHON you could probably call some python delimited file testing program from within SAS.

Re: SAS Macros: Error Handling Best Practices

Tom — Thu, 10 Sep 2026 02:06:10 GMT

Parameter validation is an important tool for making macros that are easy to use and also easy to create. The late Tom Hoffman created an excellent utility macro for implementing this idea over 30 years ago named %PARMV. You can find an updated version of the macro on github at https://github.com/sasutils/macros/blob/master/parmv.sas.

Using this macro will insure that your error messages are consistently styled and also make it easy to implement common parameter checking features such as automatic case conversion, valid value checking, default values, etc. You can read the source code of the %PARMV() macro to see what else it can, including in the comment block some simple examples.

In your example you will want to use the _VAL= parameter of %PARMV() to pass in the list of valid values. You will also want to use _DEF= parameter to pass in the fact that ALL should be the default value. And leave the _CASE= parameter at its default setting (U for uppercase).

%parmv(origin_filter,_val=all usa asia europe,_def=all)

So using this macro your example program can be reduced to this much simpler definition.

%macro origin_chart
/*-----------------------------------------------------------------------
Generate a bar chart of car counts by Type from sashelp.cars
-----------------------------------------------------------------------*/
(origin /* optional filter to a single Origin value */
);

/*-----------------------------------------------------------------------
Generate a bar chart of car counts by Type from sashelp.cars

Example calls:;
%origin_chart(all)
%origin_chart(usa)
%origin_chart(europe)
-----------------------------------------------------------------------*/
%local parmerr ;
%parmv(origin,_val=all usa asia europe,_def=all)
%if (not &parmerr) %then %do;

title "Number of Cars by Type for &origin (Ordered by Descending Frequency)";            
proc sgplot data=sashelp.cars;
%if ALL ne &origin %then %do;
  where upcase(Origin)="&origin";
%end;
  vbar Type /
      stat=freq
      categoryorder=respdesc
      group=Type
      groupdisplay=cluster
      datalabel
      nooutline
  ;
run;
title;

%end;
%mend origin_chart;

Example log:

There is another key feature of parameter checking that you did not get to that %PARMV() can help with. In a complex macro that might have multiple parameters the user might have provided more than one invalid parameter value. It is extremely frustrating to have to fix your typos or mistakes one at a time. By using the PARMERR flag variable %PARMV() will allow the coder of the macro to implement checks for all of the parameters and report all of the mistakes on the first call.

Re: Hyperlinks within text string - Export to Excel

Tom — Wed, 09 Sep 2026 16:00:37 GMT

Are you sure that is the code you ran?

line @1 'deadline ^{style [url="&ct_link" linkcolor=white]via
 the ^{style [color=blue textdecoration=underline]cost transfer form}
 available on the G&C webpage.} ';

Because the use of single quotes around the string will prevent the macro processor from replacing the reference to the CT_LINK macro variable with its value.

Try using double quotes instead.

line @1 "deadline ^{style [url='&ct_link' linkcolor=white]via
 the ^{style [color=blue textdecoration=underline]cost transfer form}
 available on the G&C webpage.} ";

It will also prevent the macro processor from replacing the reference to the C macro variable with its value. But you did not provide a value for that one.

Re: How do I catch up when I am so horribly behind?

Tom — Sat, 05 Sep 2026 16:46:41 GMT

Notice in your corrected code that MyHeart is only one word in the SET statement. So you are only trying to read from a single dataset in this step. But since you did not specify a libref it will look for it in the WORK library. Since the WORK library is generally deleted when your session ends you will need another data step that creates MYHEART before you can run this step.

If you had created a MYHEART dataset before then the three steps you have posted so far will run without error. But if you try to run them again in the new SAS session the second and third will fail since the MYHEART dataset will not exist, only the MY and HEART datasets that your first data step with the typo created.

Most often a libref will point to a single file folder and most often the name used for the libref will be the same as the name of the terminal folder in the file path that it points to, but that is not always the case. So the SASUSER libref that SAS creates when your SAS session starts normally does point to a folder named sasuser in your home directory.

If you are using SAS/Studio as your user interface remember that although the libraries that a libref points to might look like a file folder in the GUI. So make sure you are in the right place in the user interface for the type of action you are trying to take.

Saving a file, such as your program or some raw data file, is different than saving a dataset. (Even though the dataset will end up as a file with the extension of .sas7bdat). Normally to save a dataset you need to run CODE. But to save a file you can usually use the tools of the editor you are using.

If you are using SAS/Studio to edit and submit your code the file save interface does take a little getting used to. Back sure to click on the folder where you want the file written.

Re: How do I catch up when I am so horribly behind?

Tom — Sat, 05 Sep 2026 02:47:26 GMT

@Amber5 wrote:

I was able to find a file and create a data step:

data my heart;

set sashelp.heart;

run;

It did do something. It is just unbelievable that chunks of knowledge just fly out of my head, even when I hand write the codes in a notebook so I can remember what I did in my last assignment. Still lost in the woods, but it's like I at least found where I was a day or so ago.

What is your actual assignment?

Your data step will create TWO output datasets. Both of which will be copies of the input dataset named SASHELP.HEART. That is because you include two dataset names in the DATA statement. One was MY and the other was HEART, Since neither included a libref they will normally be created in the WORK library.

Note: It is completely normal for data steps to be different. Then only reason they would be exactly the same would be if you wanted them to do the exact same thing. That is generally true for any programming language. What you need to learn is what the statements do and how the syntax works so that you know what to change to make the data step do what you want for your new situation.

Re: z/OS IDCAMS DCOLLECT OUTPUT: multi-line input record with varying length, input using derived po

Tom — Sat, 05 Sep 2026 02:35:22 GMT

I don't understand what your input is as the phrase "an IDCAMS DCOLLECT run" does not mean anything to me. The text files you attached both appear to be SAS listing outputs. Are you trying to write a data step to read back in a SAS listing output file? If not then please share the an example INPUT file.

Re: How to pass macro parameter with "," ?! Is possible?

Tom — Fri, 04 Sep 2026 14:00:16 GMT

You could use macro quoting functions.

%m_test(grps=%nrstr(&grps),grps_note=&grps_note.);

But in your case it is not needed because SAS does not care if you use commas or spaces between the items listed inside the IN() operator.

%let grps=2 3;

Re: Remove decimal points in PROC FREQ cross tab

Tom — Wed, 02 Sep 2026 19:18:24 GMT

Is PROC FREQ even what you want to use for this?

I am not sure I understand what you are trying to do. Especially since you did not provide any actual data. Based on the column percents and totals you provided it looks your data looks like this:

data have ;
  input var1 :$1. @;
  do var2='One','Two';
    input count @;
    output;
  end;
cards;    
A 54 19
B 36  8
C 18  0
D 5   1
;

Why do you want to print the column percentages without the decimal places?

Why do you want to print the column percentages and the raw totals in the same column of your report?

Re: Certain keys not recognized in SAS on Mac via VMware

Tom — Tue, 01 Sep 2026 01:31:24 GMT

Explain how they are not working?

Remember that Mac keyboards do not have the equivalent of the PC delete key (delete the current character). The key marked delete on a Mac works like the backspace key on the PC keyboard (delete the previous character).

Re: How to make the macro [works on column] to work on column with BY GROUP?!

Tom — Sat, 29 Aug 2026 23:26:43 GMT

I am not sure I follow what exactly the question is.

Are you asking how to extend the steps in your macro to allow you to process all three Y variables in one pass? So your macro call might look like:

%m_concaveness(data=_concave_, x=x, y=y1 y2 y3, out=_out);

If so then add some macro logic that will add the extra statements to allow each step to handle multiple Y variables. So for example adding more assignment statements to the first data step, and more selected columns to SQL select statements.

Or are you asking how to extend the macro so that you can pass in a grouping variable?

So your macro calls might look like:

%m_concaveness(data=_concave_,group=p,x=x, y=y1, out=_out_y1);

If so then add proper BY groups into the steps in the macro.

So something like:

%macro m_concaveness(data=,group=,x=, y=, out=);
    proc sort data=&data;
        by &group &x;
    run;

    data _temp_concave;
        set &data;   
        by &group;   
         lag_y = lag(&y);
         dy = dif(&y);
         if first.&group then call missing(lag_y,dy); 
     run;

    data _prep;
        merge &data (rename=(&y=_y_prev &x=_x_prev))
              &data
              &data (firstobs=2 rename=(&y=_y_next &x=_x_next))
        ;
        by &group ;
     run;

    proc sql noprint;
        create table _second_diff as
        select a.group
            ,a.&x as x_val
            ,a.&y as y_val
            ,(c.&y - 2*a.&y + b.&y) as second_diff
        from &data as a
        left join &data as b
          on a.&group=b.&group 
            and a.&x > b.&x 
            and not exists 
               (select 1 from &data as c2 
                 where c2.group=a.group 
                   and c2.group=b.group
                   and c2.group=c.group 
                   and c2.&x > b.&x 
                   and c2.&x < a.&x) 
        left join &data as c 
           on a.&group=b.&group
             and a.&x < c.&x
             and not exists 
                  (select 1 from &data as c3
                  where c3.group=a.group 
                    and c3.group=a.group
                    and c3.group=c.group
                    and c3.&x < c.&x and c3.&x > a.&x) 
           where b.&y is not null and c.&y is not null 
      ; 
      quit; 
   proc means data=_second_diff noprint;
      by &group ;
      var second_diff;
      output out=&out mean=mean_concavity sum=sum_concavity 
                  css=variance_concavity /*concaveness*/ n=n_points
      ; 
     run; 
/* This step does nothing 
data &out; set &out; run; 
*/ 
proc sql; 
  create table &out as 
    select group 
         , avg(second_diff) as mean_concavity label="(Mean Concavity)"
         , avg(abs(second_diff)) as mean_abs_concavity label="(Mean Absolute Curvature)" 
         , sum(second_diff) as net_concavity label="(Net Concavity)" 
         , count(*) as valid_points 
     from _second_diff 
     group by &group 
     ; 
quit; 

proc datasets lib=work nolist;
 delete _second_diff _temp_concave _prep; 
quit; 

proc print data=&out noobs; 
title "Curve Overall Concaveness Measurement"; 
run; 

%mend;

Re: Any Wrong with this coding?!

Tom — Thu, 27 Aug 2026 14:13:37 GMT

@hellohere wrote:

Is this due to usage of %do instead of do inside macro?!

Become your own macro interpreter and see for yourself.

With the %DO loops the macro is generating code like:

data temp_simu_sin_od;
ybase=(round(ranuni(&i.),0.1)+0.1)*&baseratio.;
  tick=&i.;
  ynoise=(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);
  y=ybase*sin(&x./200)+(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);	
  ind=&x.;
  output;
  ynoise=(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);
  y=ybase*sin(&x./200)+(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);	
  ind=&x.;
  output;
  ynoise=(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);
  y=ybase*sin(&x./200)+(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);	
  ind=&x.;
  output;
...
ybase=(round(ranuni(&i.),0.1)+0.1)*&baseratio.;
  tick=&i.;
  ynoise=(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);
  y=ybase*sin(&x./200)+(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);	
  ind=&x.;
  output;
  ynoise=(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);
  y=ybase*sin(&x./200)+(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);	
  ind=&x.;
  output;
  ynoise=(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);
  y=ybase*sin(&x./200)+(ranuni(&i.)-0.5)*&noiseratio.*2*ranuni(&x.);	
  ind=&x.;
  output;
...

run;

So the data step will take a very long time to compile before it can even begin to generate the data.

It is always best to know what code you want the macro to emit before you start writing the macro.

Re: sas base: working with results viewer window

Tom — Tue, 25 Aug 2026 20:15:43 GMT

I assume you mean that you are using SAS Display Manager? Either on an Windows machine or on a Unix machine with an X-Windows servier.

I think you are talking about the window that kind of serves as a "table of contents" for the results you have generated.

I don't think that removing an entry from the "table of contents" view of the Results Viewer will actually remove anything from the actual ODS output file. If you want to recreate the results with some steps not run then you would need to re-run the steps in the order you want them.

Otherwise you could save the results to a file and then edit the result file using an editor of your choice.

Also I think that I have noticed in the past that you can remove content from the actual Results without them disappearing from the "table of contents" of the Results Viewer window. I remember in the past having to clear both of the windows to get back to a blank slate.

Re: Connecting all possible individuals within a family, aka connecting paired nodes

Tom — Sat, 22 Aug 2026 19:00:53 GMT

For problems that are too large for the HASH based solutions I like to use this old %SUBNET() macro that uses an iterative SQL solution.

It works with your example if you tell it the connections are bi-directional. (Note that the PERSON_ID and FAMILY_ID values need to be distinct, as is the case in your example data, so that it does not confuse PERSON number 1 with FAMILY number 1).

%subnet
(in=mydata
,out=want
,from=person_id
,to=family_id
,subnet=subnet
,directed=0
)

For your example it finds 3 extended families.

 person001 family001 1
 person001 family002 1
 person002 family001 1
 person003 family001 1
 person004 family001 1
 person005 family001 1
 person006 family001 1
 person007 family001 1
 person008 family002 1
 person009 family002 1
 person010 family002 1
 person011 family002 1
 person012 family002 1
 person013 family002 1
 person014 family002 1
 person014 family003 1
 person015 family003 1
 person016 family003 1
 person016 family004 1
 person017 family004 1
 person018 family004 1
 person019 family004 1
 person020 family004 1
 person021 family005 2
 person022 family005 2
 person023 family005 2
 person024 family006 3

Re: Strange behavior when data are written and re-read directly from an Excel file

Tom — Thu, 20 Aug 2026 14:59:18 GMT

I don't understand. If I do what you did and use ODS EXCEL to make a column with strings that all look like numbers then when I read back in the XLSX file I get a NUMERIC variable.

* Create two character strings with the same values ;
data xxx;
  length x $200;
  input x @@;
  y=x;
cards;
-1 -10 -100 -10 -1 
;

* Use PROC REPORT to write it to a spreadsheet ;
* Tell PROC REPORT to write X as String ;
ods excel file="%sysfunc(pathname(work))/temp.xlsx" 
  options (sheet_name="XXX" tab_color="white" flow="tables" )
;
proc report data=xxx;
  define x / style(column)={tagattr="type:String"};
run;
ods excel close;

* Check the data types of the two columns read from the spreadsheet ;
libname prvfndgs xlsx "%sysfunc(pathname(work))/temp.xlsx"; 
proc contents data=prvfndgs.XXX; 
run;

You cannot really compare the original string variable to a numeric variable. Perhaps your code is just getting confused by this change of variable type?

Re: SAS CDE creates SAS datasets with restrictive file permissions on SAS Grid environment

Tom — Sun, 16 Aug 2026 14:47:31 GMT

Unless you are trying to modify existing datasets using PROC DATASETS or an PROC SQL UPDATE statement (as opposed to the normal process of replacing them with a new dataset using the same name) then the permission issue is usually that the user does not have write access to the directory that the datasets live in not that they do not have write permission to the dataset file itself. Not having write access to the directory means they cannot create new files or delete existing files.

Try changing the group write bit on the directory. Also set the group sticky bit on the directory so that all files created there are in that group also.

Re: Create snowflake table as select from sql

Tom — Sun, 16 Aug 2026 14:31:09 GMT

If the goal is to move data between two different foreign databases then you should get better results if you switch to PROC FEDSQL instead.

Re: Create snowflake table as select from sql

Tom — Sat, 15 Aug 2026 01:30:46 GMT

That is a strange error. It seems to be saying it tried to read the text string 'SOLE OWNER' into a numeric variable.

Sounds like something is causing the parsing of the bulk loaded file to fail.

Do you know where in your data you have that value? If it only appears only a few times you might be able figure out what values are causing the trouble by looking at the values of a couple of observations before where it first appears.

You might have unbalanced quotes in your data fields and SAS and Snowflake don't agree on how to handle them. Or perhaps your data includes values that look like the delimiter that bulkload is using to parse the records. Or perhaps they don't agree on the encoding that is being used?