Desktop productivity for business analysts and programmers

Why i am missing 15k columns?

Reply
New User
Posts: 1

Why i am missing 15k columns?

Hello all,

i am creating tables with some prompts...in these steps i´m creating some variables with :INTO and with them the output table gets created.

 

If i am "clicking" my way to the output table i get about 22M columns, but with my code version i get 22M minus 15k.

 

I compared the "clicked" code with my code via prompts and code, its the same code @ the end (checked it with %put).

 

Its a simple code

CREATE TABLE work... as

SELECT t1.columns
       t2.columns
              from work.x t1
              LEFT JOIN y t2 ON (t1.key = t2.key)

 

Like i said, i compared the clicked code with sas eg with my written...at the end its the same, but i´m missing 15k on colums.

The output data is i think the same, i compared some rows...(btw is there a function, where i can compare 2 Tables, if the data is the same?)

 

So...why?

 

Super User
Posts: 23,997

Re: Why i am missing 15k columns?

You mean 15K rows right? 22M columns would be unmanageable...

 

Please post the full code from each query and the log. With the basic of what you've shown there's no real way to tell the difference.

 

You can use PROC COMPARE to data sets, or Tasks>Data>Compare to compare two data sets.


@JonDo wrote:

Hello all,

i am creating tables with some prompts...in these steps i´m creating some variables with :INTO and with them the output table gets created.

 

If i am "clicking" my way to the output table i get about 22M columns, but with my code version i get 22M minus 15k.

 

I compared the "clicked" code with my code via prompts and code, its the same code @ the end (checked it with %put).

 

Its a simple code

CREATE TABLE work... as

SELECT t1.columns
       t2.columns
              from work.x t1
              LEFT JOIN y t2 ON (t1.key = t2.key)

 

Like i said, i compared the clicked code with sas eg with my written...at the end its the same, but i´m missing 15k on colums.

The output data is i think the same, i compared some rows...(btw is there a function, where i can compare 2 Tables, if the data is the same?)

 

So...why?

 


 

PROC Star
Posts: 629

Re: Why i am missing 15k columns?

The maximum number of variables in SAS Datasets depends on your environment and the file's attributes.

Thanks,
Suryakiran
Respected Advisor
Posts: 4,780

Re: Why i am missing 15k columns?

[ Edited ]

@JonDo

Just as something eventually worth trying: I believe some of the prompts are quoting the parameter values (using these non-printable SAS quoting hex value). It might be worth to add %unquote() functions around the macros in your code and see if this resolves the issue.

 

You can use the SQL EXCEPT operator to get rows which are unique to your table with more records.

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001361224.htm

Super User
Super User
Posts: 8,273

Re: Why i am missing 15k columns?

Why i am missing 15k columns

Do you really have over 15 thousand columns in your data set? That would be almost impossible to work with.

 creating some variables with :INTO

Not sure what this means. The INTO clause in PROC SQL is used to create MACRO variables. Note that macro variables can contain at the most 64K bytes.  So if you are trying to put 15 thousand variable names into a macro variable it will probably run out of room.

 

 

 

Super User
Posts: 13,925

Re: Why i am missing 15k columns?

My first suspect would be that one or both of the source tables changed size somewhere between your two tests.

 

Ask a Question
Discussion stats
  • 5 replies
  • 149 views
  • 0 likes
  • 6 in conversation