Richardvan_tHoff Tracker

Re: remove special hidden ZWSP and NBSP from char column

Richardvan_tHoff — Tue, 18 Mar 2025 07:37:01 GMT

Both Solutions work. Thank you both.

remove special hidden ZWSP and NBSP from char column

Richardvan_tHoff — Thu, 13 Mar 2025 13:08:29 GMT

I import a excel file with proc import into a SAS table and than put that data into a teradata table.

I get a error when I load the data into the teradata table. Bad character.

We checked the excel file and in certain rows we saw hidden non printable characters (ZWSP en NBSP).

We are trying to filter those characters out with the following code.

DATA work.filtered;

SET work.unfiltered;

name_new = PRXCHANGE('s/\x{200B}//',-1, name); /* does not work to filter out ZWSP */

name_new = PRZCHANGE('s/\&zwsp;//',-1, name); /* does not work to filter out ZWSP */

RUN;

What am I doing wrong or is there a other solution within SAS.

Re: how te create a master detail report with Proc report

Richardvan_tHoff — Fri, 03 May 2024 05:33:55 GMT

thank you very much for your example/solution.

Re: how te create a master detail report with Proc report

Richardvan_tHoff — Thu, 02 May 2024 12:59:08 GMT

example I used to create the report.

DATA work.test ;

LENGTH procesnaam $15.;

LENGTH proces_start_date 8.;

LENGTH proces_end_date 8.;

LENGTH proces_detail $25.;

LENGTH subproces $15.;

LENGTH sub_start_date 8.;

LENGTH sub_end_date 8.;

LENGTH start_place $15.;

LENGTH end_place $15.;

LENGTH comments $50.;

INFILE datalines dlm=",";

INPUT procesnaam$ proces_start_date :ddmmyy12. proces_end_date :ddmmyy12. proces_detail$ subproces$ sub_start_date :ddmmyy12. sub_end_date :ddmmyy12. start_place$ end_place$ comments$;

FORMAT proces_start_date ddmmyy10.;

FORMAT proces_end_date ddmmyy10.;

FORMAT sub_start_date ddmmyy10.;

FORMAT sub_end_date ddmmyy10.;

datalines4;

walking,01-01-2024,10-04-2024,I walk very slow,part1,01-01-2024,10-01-2024,New york,Amsterdam,I started very well

walking,01-01-2024,10-04-2024,I walk very slow,part1,01-01-2024,10-01-2024,New york,Amsterdam,The walk was nice

walking,01-01-2024,10-04-2024,I walk very slow,part2,10-01-2024,24-01-2024,Amsterdam,Berlin,Was perfect

swimming,10-06-2024,10-07-2024,I love swimming,first swim,10-06-2024,15-06-2024,Leeuwarden,Sneek,Starting was difficult

swimming,10-06-2024,10-07-2024,I love swimming,first swim,15-06-2024,30-06-2024,Leeuwarden,Sneek,Injured myself

;;;;

RUN;

Re: how te create a master detail report with Proc report

Richardvan_tHoff — Thu, 02 May 2024 09:31:02 GMT

I already stated that the result will be converted to PDF.

I will make a example in data lines and add that later on.

how te create a master detail report with Proc report

Richardvan_tHoff — Thu, 02 May 2024 08:21:52 GMT

I want to create a master detail subdetail report within SAS BASE (proc report).

You woud have a master row with colums. The master has detail rows with different columns and detail row also subdetail rows with comments.

I don't want all the columns master,detail, subdetail on one row and master, detail, subdetail should be on the same page.

The result will be converted to pdf.

The output would look something like the below example:

Procesname Start date end date Detail info

-----------------------------------------------------------------------------------------

Walking 01-01-2024 10-04-2024 I walk very slow

Subproces start date end data Start place end place

-----------------------------------------------------------------------------------------------------

Part 1 01-1-2024 10-01-2024 New York Amsterdam

Comments

--------------------------------------------------------------------------------------------

I started very well

The walk was nice

Subproces start date end data Start place end place

-----------------------------------------------------------------------------------------------------

Part 2 10-1-2024 24-01-2024 Amsterdam Berlin

Comments

--------------------------------------------------------------------------------------------

Was perfect

swimming 10-06-2024 10-07-2024 I love swimming

detail

subdetail

Re: Resolve macro at execution

Richardvan_tHoff — Wed, 19 Aug 2020 08:39:03 GMT

Thank you all for your suggestions. For the problem I have it will to work but not the way I want it.

I think it is not possible to change the value dynamically at execution time. I was looking for such a solution.

I am working on a stored proces to transfer data en code from development to production. In that STP I have prompts to choose source and target (which are based on a column from a sas table in the metadata). Knowing that i solved the problem a other way.

%LET g_sytem=acc;

%LET g_system_folder_code=g:\sasdiv\program\&g_system.\code;

From the stp I get the variables p_source en p_target;

p_source=ont;

p_target=prod;

%LET v_source_code_source =%SYSFUNC(TRANWRD(&g_system_folder_code.,\&g_system.\,\&p_source.\));

%LET v_source_code_target =%SYSFUNC(TRANWRD(&g_system_folder_code.,\&g_system.\,\&p_target.\));

This way i still use the standard available macro variable's.

Resolve macro at execution

Richardvan_tHoff — Wed, 19 Aug 2020 07:16:27 GMT

%LET location=g:\sasdiv01\;

%LET env=prod;

%LET folder_code=&location.&env.\code;

The above works perfect, but now I only want to change the env at execution in my code.

What I want as a result is the following.

%LET env=test;

%LET folder_test=&folder_code;

%PUT &=folder_test;

Current Result

folder_test=g:\sasdiv\prod\code

Desired Result

folder_test=g:\sasdiv\test\code

I know it is not possible with the code i wrote above. Is there a way to do this a other way. The reason I want to this is because all the references to folders and other stuff is in the 1 piece of code. We don't want any references to folders in the code directly (if we change a folder we only have to change it in that first piece of code).

Re: Read Access Violation STPX_LP

Richardvan_tHoff — Thu, 22 Nov 2018 22:05:25 GMT

It's occuring while running the Stored Procedure Web application.

I think it does not happen within EG.

Read Access Violation STPX_LP

Richardvan_tHoff — Thu, 22 Nov 2018 15:11:46 GMT

The last 1 1/2 week we get from time to time the below error when we use stored processes.

SAS info:

SYSVER 9.4
SYSVLONG 9.04.01M1P120413
SYSVLONG4 9.04.01M1P12042013

Error message:

ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:

The SAS task name is [STPX_LP]
ERROR: Read Access Violation STPX_LP
Exception occurred at (03772501)
Task Traceback
Address Frame (DBGHELP API Version 4.0 rev 5)
0000000003772501 000000000E58E200 sasyh:tkvercn1+0x414C1
0000000002357AF3 000000000E58E208 sashost:Main+0x70663
00000000037D2253 000000000E58E428 sase7:tkvercn1+0x1213
00000000037783F3 000000000E58EE20 sasyh:tkvercn1+0x473B3
00000000037706E7 000000000E58F190 sasyh:tkvercn1+0x3F6A7
0000000003945D2C 000000000E58F198 sasxshel:tkvercn1+0x24CEC
00000000039230D6 000000000E58F600 sasxshel:tkvercn1+0x2096
00000000039219D5 000000000E58F690 sasxshel:tkvercn1+0x995
000000000394AAA0 000000000E58F7D0 sasxshel:tkvercn1+0x29A60
0000000003973E4A 000000000E58FAB0 sasxshel:tkvercn1+0x52E0A
000000000397734C 000000000E58FBA0 sasxshel:tkvercn1+0x5630C
0000000003979968 000000000E58FBF0 sasxshel:tkvercn1+0x58928
00000000022F839B 000000000E58FF20 sashost:Main+0x10F0B
00000000022FDFFD 000000000E58FF50 sashost:Main+0x16B6D
00000000778D59CD 000000000E58FF58 kernel32:BaseThreadInitThunk+0xD
0000000077B3385D 000000000E58FF88 ntdll:RtlUserThreadStart+0x1D

Re: Replacing * with % in a string results in a warning in the log

Richardvan_tHoff — Tue, 12 Jul 2016 10:18:46 GMT

THx. that worked and you are correct you don't need sysfunc in a data step.

Replacing * with % in a string results in a warning in the log

Richardvan_tHoff — Tue, 12 Jul 2016 08:47:05 GMT

I am trying to replace * with % in a string and than build a SQL like statement. Replacing it with tranwrd works perfect and can even use a macro variable to build the like statement.

The only problem I have is that I get a warning in the log :

WARNING: Apparent invocation of macro G not resolved.

This is obviously because of the % replacement. I tried several hiding statements but i cant get rit of the warning. Using the macro variable in the SQL statement works also perfect as you can see in the enclosed code.

OPTIONS MPRINT NOMLOGIC NOSYMBOLGEN source source2;
%MACRO multi_selection(p_variabele=,p_type=);
  %GLOBAL &p_variabele;
  %GLOBAL &p_variabele._zonder;
  %GLOBAL &p_variabele._sql;

  %LET v_scheiding=;
  %IF &p_type=txt %THEN %LET v_scheiding=%STR(%");
  %if &&&p_variabele._count=1 %THEN
     %DO;
        %LET &p_variabele._zonder=&&&p_variabele.;
        %LET &p_variabele=&v_scheiding.&&&p_variabele.&v_scheiding.;
        %LET test=&&&p_variabele._zonder;
        %IF %SYSFUNC(FIND(&&&p_variabele.,*)) > 0 %THEN 
          %DO; 
             DATA _NULL_;
               test1="%SYSFUNC(TRANWRD(&&&p_variabele._zonder,*,%))"; 
               test="LIKE '" || test1 || "'";
               CALL SYMPUT("&p_variabele._sql",test);
             RUN;
          %END;
     %END;
  %else   
     %DO;
     %LET &p_variabele=;
     %do i=1 %to &&&p_variabele._COUNT;
        %IF &i=1 %THEN
          %DO;
            %LET &p_variabele._zonder=&&&p_variabele.&i;          
            %LET &p_variabele=&v_scheiding.&&&p_variabele.&i.&v_scheiding.;
          %END;
        %ELSE
          %DO;
            %LET &p_variabele._zonder=&&&p_variabele._zonder.,&&&p_variabele.&i.;        
            %LET &p_variabele=&&&p_variabele.,&v_scheiding.&&&p_variabele.&i.&v_scheiding.;
          %END;
     %END; 
     %END;
     
  %PUT &&p_variabele._zonder: &&&p_variabele._zonder;
  %PUT &&p_variabele.: &&&p_variabele.;
  %PUT &&p_variabele._sql:  &&&p_variabele._sql;
%MEND;

%LET test_sql=;
%LET test_count=1;
%LET test=*g*;

%multi_selection(P_variabele=test,p_type=txt);

DATA work.test;
  txt_field="aabbd";
  output;
  txt_field="agod";
  output;
  txt_field="g987b";
  output;
  txt_field="987bg";
  output;
RUN;

PROC SQL;
  CREATE TABLE work.test1
  AS
  SELECT
    *
  FROM
    work.test
  WHERE 
    txt_field &test_sql.;
QUIT;

Re: xlsb export problem with big number of rows and columns

Richardvan_tHoff — Tue, 12 Apr 2016 11:40:27 GMT

a) first of all that was a test the results will never be as big. More columns less rows but still a problem. I tested it also with 360.000 rows and 70 columns and got it to 15mb in size with xlsb. Maybe you learned something.

b) stick to data transfer you may. What I was saying before you can call everything data transfer. When you have a end user stick by your plan offering them csv files and for every sheet a different one and tell them to create there own excel sheet (customer is not important).

But thx for not helping. Did some more digging and found the real ploblem.

http://support.sas.com/kb/50/658.html.

Re: xlsb export problem with big number of rows and columns

Richardvan_tHoff — Tue, 12 Apr 2016 11:28:43 GMT

It is not data transfer. I know json I know csv I know xml and the email is send to the person who starts the stored proces (which got autorisation). For data transfer I use DI-studio.

I only descripe a small part of the proces in which something is going wrong.

Just look at the problem at hand.

Re: xlsb export problem with big number of rows and columns

Richardvan_tHoff — Tue, 12 Apr 2016 11:02:22 GMT

First of all it is not data transfer and csv is not the solution. Beside that every excel sheet is data transfer. The people who receive this data don't want a csv file.

I just made an example and the end product is much more sheets than one in the excel file.

xlsb export problem with big number of rows and columns

Richardvan_tHoff — Tue, 12 Apr 2016 07:11:44 GMT

DATA work.export;
  DO i=1 to 10000;
    DO j=1 to 50;
      a=i;
      b=j;
      c=i/j;
      d=j/i;
      e=c*d;
      f=e/c;
      g="Hi " || a || b;
      OUTPUT;
    END;
  END;
RUN;

PROC EXPORT  DATA=work.export DBMS=excelcs
  OUTFILE="g:\kcbiar\stp\test.xlsb" REPLACE LABEL;
  SHEET="Detail";
RUN;

We are using proc export to create a excel file and send it within SAS by e-mail. This works great with xlsx output, but the problem with that format is that the size of the file is very big with a lot of rows an columns (say 300.000 and 20 columns is 81MB even after using ods to zip it is still 52MB). Such a big file we cannot send by e-mail and we don't want to save the data somewhere for the user. We are sending it by e-mail because the query can take up to 2 hours to complete.

To make the excel sheet smaller we could use the xlsb format (sample code included). That works perfect except when we increase the number of row and columns the excel sheet is empty. In the example i used 500.000 rows and 9 columns. The file size is 9kb and the excel sheet is empty and the name of the sheet is not "Detail" but "A266FF2A662E84b639DA". I thought maybe the old limitation of around 65000 rows in excel was the problem, so i reduced the number of rows to 35000. Still the same result. If I reduce the number of rows to 10000 than it works great or if I reduce the number of columns to 2 than I can use 65000 rows.

It looks like the combination of rows and columns limits the output. How is this possible and is there something I can do to fix that.

We have SAS 9.4 and offcourse the pcfile server also.

proc export dmbs=xls strange sheet name

Richardvan_tHoff — Wed, 21 May 2014 08:57:53 GMT

I use proc export in SAS EG (9.3 windows server side) to create a xls file.

The strange thing is if I use a sheet name that starts with a number, the first number is replaced with _.

Why is the first number replaced with a _.

DATA work.test;

a=1;

b=2;

c=3;

output;

a=3;

b=2;

c=1;

RUN;

PROC EXPORT DATA=work.test

OUTFILE="test.xls"

DBMS=XLS

REPLACE;

SHEET="123";

RUN;

Re: Split h1-h10 into h1,h2,h3,h4,.... h10

Richardvan_tHoff — Mon, 16 Sep 2013 13:40:10 GMT

Thanks all.

I finally got it working for everything (even for my last problem).

My solution

data work.testje;

input var $ 100.;

length new_var $ 20;

do _j=1 to countw(var,',');

_var=scan(var,_j,',');

if find(_var,'-') then do;

_v1=scan(_var,1,'- ');

_v2=scan(_var,2,'- ');

do _i=prxchange('s/(\w*\D+)(\d+)/$2/',-1,_v1) to prxchange('s/(\w*\D+)(\d+)/$2/',-1,_v2);

_len_new=LENGTH(STRIP(_i));

_len_old=LENGTH(SUBSTR(STRIP(_v1),(LENGTH(STRIP(_v1))-ANYALPHA(STRIP(REVERSE(_v1)))) +2));

_new_i=REPEAT('0',_len_old - _len_new - 1)||STRIP(_i);

_turned=REVERSE(STRIP(_v1));

SUBSTR(_turned,1,_len_old)=REVERSE(STRIP(_new_i));

new_var=REVERSE(STRIP(_turned));

output;

end;

else do;new_var=left(_var);output;end;

end;

cards;

G1, G2, G4, X1 -X3, H051-H055, T10,NA03ME1-NA03ME3, 34WE100-34WE103,001-008, 1-10, H0001- H0010

Re: Split h1-h10 into h1,h2,h3,h4,.... h10

Richardvan_tHoff — Wed, 11 Sep 2013 12:17:59 GMT

Thanks all. I am using the code already from hai.kuo. Only made a minor change, because the variable could also contain only numbers.

data test;

input var $ 80.;

length new_var $ 20;

do _j=1 to countw(var,',');

_var=scan(var,_j,',');

if find(_var,'-') then do;

_v1=scan(_var,1,'- ');

_v2=scan(_var,2,'- ');

do _i=prxchange('s/(\w*\D+)(\d+)/$2/',-1,_v1) to prxchange('s/(\w*\D+)(\d+)/$2/',-1,_v2);

IF(prxmatch("/\D+/",strip(_v1))) THEN

new_var=cats(prxchange('s/(\w*\D+)(\d+)/$1/',-1,_v1),_i);

ELSE

new_var=_i;

output;

end;

else do;new_var=left(_var);output;end;

end;

drop _:;

cards;

G1, G2, G4, X1 -X3, H51-H55, T10,NA3ME1-NA3ME3, 34WE100-34WE103, 1-10

;

The only problem I am trying to solve is when you have H01-H10. The above code would split it in the below rows

...

H10

While it should be

H01

H02

H03

H04

...

H10

Split h1-h10 into h1,h2,h3,h4,.... h10

Richardvan_tHoff — Mon, 02 Sep 2013 11:59:24 GMT

I want to translate the contents of a column.

For example, the content includes the following value (this is the content of 1 column in 1 row):

G1, G2, G4, X1 -X3, H51-H55, T10

I want to translate that to different rows.

The result should look like this:

H51

H52

H53

H54

H55

T10

I have not problems making rows of the single ones (I mean G1 G2 G4 and T10). The function scan works perfectly.

The problem is how I translate X1-X3 into X1, X2, X3 and the same for H51-H55.