sas programming

[Satora_In]

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;

[PaigeMiller]

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!

[andreas_lds · Accepted solution]

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.

[Satora_In]

I asked for a solution 

[PaigeMiller]

---

@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.

[ballardw]

---

@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.

[Satora_In]

my code works, I want to improve it

[Kurt_Bremser]

• 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)?

[ballardw]

"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-dat... 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.

[Satora_In]

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