Versuche macro Ut_varlist. Sie müssen das Macro einmal rünterlafen und speichern Aber die Parameter ergeben genau das erwünschte Ergebnis in einen einzelnen Aufruf. https://communities.sas.com/t5/SAS-Programming/Macro-function-to-return-a-list-of-variables-in-a-table-in-a/td-p/633536 ... View more

If the engine does not support the ATTRN with NLOBS, because the dataset is a view, or has an active where clause, or the input is a database table then the following alternative is useful. Of course  we could use and SQL query with count(*), but then the macro can no longer be used as a function in syntax like %if %ut_nlobs(...) gt  0 %then %do;     /* Handle table with more than 0 rows  */ %end;   %macro ut_nlobs( dataset_pos_parm, /* table as postional parameter */ dataset=/* in: data set to check obervations in*/, table = /* alias parameter */, data= /* alias parameter */, open_fail_err = Y /* If N then the word NOTE is added in front of sysmsg if the table cannot be opened. Sysmsg generally starts with ERROR. Use this parameter when you dont want an error on the log. */ ); /*Module------------------------------------------------------------------------------------------ + determines no of (non-deleted) records in a table + + can be used anywhere in a SAS program including within SAS proc or data step. If the table does + not exist, it returns -1. If the table exists, but cannot be opened it returns -999 + Allow dfferent parameter names, also single positional parm is allowed! +------------------------------------------------------------------------------------------Module*/ /*Hist--------------------------------------------------------------------------------------------- + created / adapted Dave Prinsloo + Jun2016 Dave Prinsloo + added open_fail_err option +--------------------------------------------------------------------------------------------Hist*/ /*cols-10--------20--------30--------40--------50--------60--------70--------80--------90--------*/ %local dsid /* Dataset-Id */ n /* Anzahl Saetze */ rc /* returncode */ open_fail_msg_word ; %put NOTE: Executing &sysmacroname. for table = &dataset_pos_parm. &table. &data. &dataset.; %if %length(&dataset_pos_parm.) ne 0 %then %let dataset = &dataset_pos_parm. ; %else %if %length(&table.) ne 0 %then %let dataset = &table.; %else %if %length(&data.) ne 0 %then %let dataset = &data.; %let dsid = %sysfunc(open(&dataset)); %if &dsid %then %do; /* get number of logical observations */ %let n = %sysfunc(attrn(&dsid,NLOBS)); %if &n. = -1 %then /* if the engine does not support NLOBS, then use * NLOBSF to count the records. This has been tested * Oracle and SAP engines */ %let n = %sysfunc(attrn(&dsid.,nlobsf)); %let rc = %sysfunc(close(&dsid)); %end; /* get number of logical observations */ %else %do; %if %sysfunc(exist(&dataset)) %then %let n = -999; %else %let n = -1; /* Log error message- could not open dataset*/ %if &open_fail_err. = N %then %put NOTE: Macro &sysmacroname. setting dummy no of records as &dataset could not be opened; %else %put %sysfunc(sysmsg()); %end;&n %mend ut_nlobs;   ... View more

First download a very generic macro %UT_VARLIST  (ut_varlist.sas)  See https://communities.sas.com/t5/SAS-Programming/Macro-function-to-return-a-list-of-variables-in-a-table-in-a/m-p/633536  The macro is very useful for many other things!      Then in your macro, simply use the code: %if %length(%ut_varlist(table=&transposed_table., exclude=_NAME_)) ne 0 %then %do;   /* The transposed table variables other than _NAME_ */ %end; %else %do; %put WARNING: Table &transposed_table. created as a transpose of &input_table. has too few variables; %end; ... View more

Use macro variables, eg  %let query_date = '2022-01-01'; proc sql noprint; connect using TD; create table LIB.TEMP as select * from connection to TD; (select * from SCHEMA.TEMP where date_value = &query_date.); disconnect from TD; quit; This means you add all your dynamic parts at the beginning of your code.    The solution above is explain the concept but and does help you prevent changing the code with each run.     To do that you can use a couple of different mechanisms create an additional sas program that contains statements like the %let statement that can be used to parametrize all you programs, and then %include this new program in all programs that need parameterization.  You then only need to change a single file before each run. You can use the sysget function to initializes the macro variable query_date from an external system variable that may be set  by a scheduler.  You can derive the value of query_date using a function INTNX('YEAR',today(),0)  and then use a put function with the appropriate date format to set the macro variable using call symputx. You can combine the above concepts, i.e. create a file to include in all your programs - but set the macro variables dynamically where possible.  ... View more

REGex ist sicherlich sehr mächtig, abder ich bevorzüge die bewährte Functionen weil das Lesen für mich einfacher ist. Was nicht für mich klar ist, aber ich have die trennende Sonderzeichen beibehalten.   Und kann auch Komma und Punkt als Trennzeichen vorkommen (Also, nicht nur in Zahlen).   data test; input have $char30.; length = length(have); /* Position Zahl vorne - Finde erste Zeichen, kein Zahl und auch nicht , oder .*/ split_pos_zahl=findc(have,'.,','DK'); /* Position Sonderzeichen Finde erste nicht-alphabetisch Zeichen */ split_pos_sonder = findc(have,'ÄÖÜäöüß','AK'); /* Gross/klein : Erste Grosszeichen, ignoriere 1. */ split_pos_gross = findc(have,'ÄEÖÜ','U',2); if 1 < split_pos_zahl < length then best_pos = split_pos_zahl; else if 1 < split_pos_sonder < length then best_pos = split_pos_sonder; else if 1 < split_pos_gross < length then best_pos = split_pos_gross; if Best_pos > 1 then want = substrn(have,1,best_pos-1)||' '||substrn(have,best_pos); cards; Brief/Post ERF!RSA SOMMER:Winter PostWichtige 225,00Freitext 15.01.2021Freitext 2RSA: ; run; ... View more

I suggest you start by writing a data step to change the 99s to blanks. Use the results of the proc freq to see what else you need to change. ... View more

did this work? ... View more

Run the conversion program once now, and every time immediately after importing / reading from the source table ... View more

This has happened to me, when I got data that had been previously transferred incorrectly between Latin and UTF8. The following code fixed it for me.    /* adjust the following to add all corrupted German char variables */ %let vars_with_corrupted_chars=name address ; data corrected; set has_incorrect_german_chars; array __array_char_vars $ &vars_with_corrupted_chars.; /* following code using explicit special characters is a LOT faster than the equivalent code below using hex values */ __array_char_vars = prxchange('s/Ä/Ä/',-1,__array_char_vars); __array_char_vars = prxchange('s/ä/ä/',-1,__array_char_vars); __array_char_vars = prxchange('s/€Ã/ä/',-1,__array_char_vars); __array_char_vars = prxchange('s/Ö/Ö/',-1,__array_char_vars); __array_char_vars = prxchange('s/ö/ö/',-1,__array_char_vars); __array_char_vars = prxchange('s/ü/ü/',-1,__array_char_vars); __array_char_vars = prxchange('s/Ãœ/Ãœ/',-1,__array_char_vars); __array_char_vars = prxchange('s/ÃŒ/ü/',-1,__array_char_vars); __array_char_vars = prxchange('s/ß/ß/',-1,__array_char_vars); __array_char_vars = prxchange('s/é/É/',-1,__array_char_vars); __array_char_vars = prxchange('s/²/2/',-1,__array_char_vars); __array_char_vars = prxchange('s/³/3/',-1,__array_char_vars); __array_char_vars = prxchange("s/´/'/",-1,__array_char_vars); run; ... View more