topic Why i am missing 15k columns? in SAS Enterprise Guide

Why i am missing 15k columns?

JonDo — Wed, 28 Mar 2018 14:39:08 GMT

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?

Re: Why i am missing 15k columns?

Reeza — Wed, 28 Mar 2018 14:51:48 GMT

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?

Re: Why i am missing 15k columns?

SuryaKiran — Wed, 28 Mar 2018 14:52:32 GMT

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

Re: Why i am missing 15k columns?

Patrick — Wed, 28 Mar 2018 21:44:50 GMT

@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

Re: Why i am missing 15k columns?

Tom — Wed, 28 Mar 2018 21:55:02 GMT

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.

Re: Why i am missing 15k columns?

ballardw — Wed, 28 Mar 2018 22:05:20 GMT

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