topic Re: sas programming in SAS Programming

sas programming

Satora_In — Wed, 18 Oct 2023 08:56:14 GMT

Dear guys,

I upload a different number of columns every day and I want to write a script so that the macro accepts columns regardless of the number. I wrote a request and I can’t do it. help me please

%_eg_conditional_dropds(WORK.QUERY_FOR_TRNSTRANSPOSED_0000);

%macro dynamic_query(input_table, output_table);

%let column_list = TELEPHONE1, ZAKAZ_NUMBER;

%do i = 3 %to 100;

%let column_list = &column_list, STOLOBC&i;

%end;

proc sql;

create table %output_table as

select distinct %column_list,

catx(";", %do j = 1 %to 100;

%if j ne 1 %then %do; %end;

input(put(STOLOBC&j, BEST11.), $FREE11.)

%end

) AS SKU

FROM &input_table;

quit;

%mend;

%dynamic_query(work.trnstransposed, work.query);

PROC SQL;

CREATE TABLE WORK.QUERY_FOR_TRNSTRANSPOSED_0000 AS

SELECT DISTINCT t1.TELEPHONE1 AS TELEPHONE1,

t1.ZAKAZ_NUMBER AS ZAKAZ_NUMBER,

/* SKU */

LENGHT 280

AS SKU

FROM WORK.TRNSTRANSPOSED t1;

QUIT;

better subject line needed

PaigeMiller — Wed, 18 Oct 2023 10:07:28 GMT

We're going to try to help you, but you need to help us as well. Please go back to your ORIGINAL post and change the subject line so that it is a brief but meaningful description of the problem. A subject line that says "sas programming" could be used on every message, but it is not meaningful and not descriptive. Thanks!

Re: sas programming

andreas_lds — Wed, 18 Oct 2023 11:03:56 GMT

An advice recommended many times: always start without any macro statements and variables. Test the code, then wrap it into a macro, and replace the things that need to be dynamic, one after the other, with a test between each modification.

Re: sas programming

ballardw — Wed, 18 Oct 2023 11:32:46 GMT

"Can't do it" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

Since your code involves a macro you should set the system option MPRINT before executing the macro so the Log will have details of the code generated.

options mprint;

%dynamic_query(work.trnstransposed, work.query);

options nomprint; /* turn off the option*/

Since you are using a custom informat, $Free, you may need to include the definition of that as well.

This defines a string containing values that start at Stolobc3 to 100

%do i = 3 %to 100;
%let column_list = &column_list, STOLOBC&i;
%end;

i=3  column_list= TELEPHONE1, ZAKAZ_NUMBER, STOLOBC3

Then here you use STOLOBC2. So I suspect something doesn't quite align.

%do j = 1 %to 100;
%if j ne 1 %then %do; %end;
input(put(STOLOBC&j, BEST11.), $FREE11.)
%end

That input expects 99 different variables to already exist in the set &input_table. How sure are you of all of those variables already existing?

You state that you want to "upload" (whatever that means) a "different number of columns". I don't see any attempt deal with different number of "columns" (SAS terminology is usually variables) just one long semicolon delimited value called SKU

input(put(STOLOBC&j, BEST11.), $FREE11.) will right justify the results of the Put which means the result will have leading spaces when fewer than 11 print positions get used by BEST ( if the value is 5 result is " 5") so if your Informat does not define what to do with leading spaces your output from the Input function is going to be suspect.

This may want

input(put(STOLOBC&j, BEST11. -L), $FREE11.)

The -L says to left justify the result. So there won't be leading spaces.

I've never used Catx quite the way you have. Normally without a defined length for the resulting variable I would expect the result to be truncated to 200 characters but sometimes Proc SQL does stuff I don't expect. If your results of the $free informat are 2 characters the resulting catx would be 296 characters long (99*3 -1 for the last value not having the ; following).

A description of what you think this is supposed to be wold be nice as well.

Re: sas programming

Satora_In — Wed, 18 Oct 2023 11:47:04 GMT

I asked for a solution

Re: sas programming

PaigeMiller — Wed, 18 Oct 2023 13:02:31 GMT

@Satora_In wrote:

I asked for a solution

So show us the working code for one or two iterations without macros and without macro variables first, as @andreas_lds said, and we can probably help you turn this into a macro. We need you to provide information, we ask for specific information; and we can't do it without that information.

And of course, provide a meaningful subject line in your ORIGINAL post.

Re: sas programming

ballardw — Wed, 18 Oct 2023 14:17:41 GMT

@Satora_In wrote:

I asked for a solution

And did not describe the problem(s). " I can’t do it." especially without a description of the code is supposed to do makes it pretty hard to recognize a solution.

Re: sas programming

Satora_In — Wed, 18 Oct 2023 19:13:09 GMT

E PROC SQL;
CREATE TABLE WORK. QUERY FOR TRNSTRANSPOSED 0000 AS SELECT DISTINCT t1. TELEPHONE1 AS TELEPHONE1, t1. ZARAZ NUMBER AS ZAKAZ NUMBER,
/w gRit W
(catx (;' input (put (t1. "Столбец1 'n, BEST11. ), $FREEl1. ) , input (put (t1. "Столбец2'n, BEST11. ), $FREE11.
input (put (+1. 'Столбец3'п,
BEST11. ),$FREE11. ), input (put (t1. 'Столбец4 'n, BEST11. ), $FREE11. ) , input (put (t1.
"Столбец5 'n, BEST11. ), SFREE11.
input (put (t1. 'Столбец6'n. BEST11 ),$FREE1Í. ) , input (put (+1. 'Столбец7 'n, BEST11. ),$FREE11. ) , input (put (t1.
"Столбец8 'n, BEST11. ) ,$FREE11.)
input (put (t1. 'Столбец4'n, BEST11
) , $FREE11. ) , input (put (t1. 'Столбец4'n, BEST11. ), $FREE11. ), input (put (t1.
"Столбец4 'n, BEST11. ), $FREE11
input (put (t1. 'Столбец4 'n, BEST11. ), $FREE11. ) , input (put (t1. 'Столбец9'n, BEST11. ),$FREE11. ), input (put (t1
"Столбец10'n, BEST11.
) , $FREE11.)
input (put (t1. 'Столбец11'n,
BEST11
) "SFREE11. ), input (put (t1. "Столбец12 'n, BEST11. ), $FREE11.
) , input (put (t1. 'Столбец13'n, BEST11. ) , SFREE11.) input (put (t1. 'Столбец14'n, BEST11 . ) , $PREE11.) , input (put (t1. "Столбец15'n, BEST11. ) , $FREE11.
) , input (put (+1. "Столбец16'n, BEST11. ),$FREE11
,input (put (t1. 'Столбец17'n, BEST11
) , $FREE11. ) , input (put (t1. 'Столбец18'n, BEST11. ), $FREE11.
) ,input (put (t1. 'Столбец19'n, BEST11. ), $FREE11. ).
input (put (t1. 'Столбец20 "n, BEST11. ), SFREE11. ), input (put (t1. 'Сполбец21'n, BEST11. ), $FREE11.
) , input (put (t1. 'Столбец22'n, BEST11. ),$FREE11. ).
input (put (t1. 'Столбец23'n, BEST11. ), $FREE11. ), input (put (ti. "Столбец24'n, BEST11. ), $FREE11.
) , input (put (t1. 'Столбец25 'n, BEST11. ), $FREE11.
input (put (t1. 'Столбец26'n, BEST11.
).
) ,$FREE11. ),input (put (t1. 'Столбец27'n, BEST11. ), $FREE11. )))
LENGTH=280 AS SKU
FROM WORK.TRNSTRANSPOSED t1;
QUIT;

This code is working. I want it to always create a different number of columns

Re: sas programming

Satora_In — Mon, 23 Oct 2023 06:31:30 GMT

my code works, I want to improve it

Re: sas programming

Kurt_Bremser — Mon, 23 Oct 2023 07:09:31 GMT

What is the purpose of the transposed dataset?
Do the (stupid IMO) name literals result from the use of an ID variable during transposition?
What is the definition of the $FREE informat (show the PROC FORMAT code)?