BookmarkSubscribeRSS Feed
JonDo
Calcite | Level 5

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?

 

5 REPLIES 5
Reeza
Super User

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?

 


 

SuryaKiran
Meteorite | Level 14

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

Thanks,
Suryakiran
Patrick
Opal | Level 21

@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

Tom
Super User Tom
Super User

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.

 

 

 

ballardw
Super User

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

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1383 views
  • 0 likes
  • 6 in conversation