Desktop productivity for business analysts and programmers

Top 100 row Not self Typing Sql

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Top 100 row Not self Typing Sql

Hi Gurus,

Are there any way to import two datasets with let say 1000 rows each n append it together without editing sql programming, in other word, eg will auto fetch in top xx for me.

thx in advance

Accepted Solutions
Solution
‎02-14-2017 08:30 AM
Grand Advisor
Posts: 17,308

Re: Top 100 row Not self Typing Sql

It should be in Query builder under an options tab. 

 

It either gives you the option or allows you to type out obs=1000. 

 

I don't have access to EG at the moment to confirm but will later today. 

View solution in original post


All Replies
Contributor
Posts: 37

Re: Top 100 row Not self Typing Sql

 
Esteemed Advisor
Posts: 6,636

Re: Top 100 row Not self Typing Sql

First of all, a dataset is not imported, as it already exists in SAS format (.sas7bdat). A dataset may be copied from somewhere and is then simply read.

External data, OTOH, needs to be imported into datasets before you can use it in SAS.

So the crucial question here is, what do you have? External data (.csv, Excel, some text, ...) or SAS datasets?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 37

Re: Top 100 row Not self Typing Sql

Hi KurtBremer,

 

I am connecting to Oracle server by libname, then drag table from left pane under server

 

 

thanks

Esteemed Advisor
Posts: 6,636

Re: Top 100 row Not self Typing Sql

So you have a libname for Oracle. Let's assume this is oralib, and your Oracle tables are table1 and table2.

Then do this.

data want;
set
  oralib.table1 (obs=1000)
  oralib.table2 (obs=1000)
;
run;

Since want is a 1-level name, the dataset will be created in library WORK.

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Grand Advisor
Posts: 17,308

Re: Top 100 row Not self Typing Sql

I assume you're looking for a method without writing code, ie using tasks?

Contributor
Posts: 37

Re: Top 100 row Not self Typing Sql

[ Edited ]

Hi Reeza,

 

Yes, just wondering if there is any function like select top X under Tasks-> Data,

 

as less coding as possible.

 

Thanks

Solution
‎02-14-2017 08:30 AM
Grand Advisor
Posts: 17,308

Re: Top 100 row Not self Typing Sql

It should be in Query builder under an options tab. 

 

It either gives you the option or allows you to type out obs=1000. 

 

I don't have access to EG at the moment to confirm but will later today. 

Contributor
Posts: 37

Re: Top 100 row Not self Typing Sql

[ Edited ]

Hi Reeza,

 

Yes you are right. Thanks

Esteemed Advisor
Posts: 6,636

Re: Top 100 row Not self Typing Sql


SASNE wrote:

Hi Reeza,

 

Yes you are right. Thanks man


You might run into trouble Smiley Wink

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Grand Advisor
Posts: 17,308

Re: Top 100 row Not self Typing Sql

Fixed it for you Smiley Wink

 


SASNE wrote:

Hi Reeza,

 

Yes you are right. Thanks woman


Your next question should be a new one, but I think you've already run into the limitations of the GUI  TASKS. 

This is a common question so if you search 'collapse rows into a single row' you'll find a lot of code examples. 

Esteemed Advisor
Posts: 6,636

Re: Top 100 row Not self Typing Sql


Reeza wrote:

Fixed it for you Smiley Wink

 

 


SASNE wrote:

Hi Reeza,

 

Yes you are right. Thanks woman


 



Would never use that. Lady is the right word.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Grand Advisor
Posts: 17,308

Re: Top 100 row Not self Typing Sql


KurtBremser wrote:

Reeza wrote:

Fixed it for you Smiley Wink

 

 


SASNE wrote:

Hi Reeza,

 

Yes you are right. Thanks woman


 



Would never use that. Lady is the right word.




 

It sounded weird when I typed it. Just Thanks works as well Smiley Happy

Contributor
Posts: 37

Re: Top 100 row Not self Typing Sql

Hi Reeza,

 

Hope you can help with one more question, do you know how to  catx  group by in query builder?

 

e.g.

 

Dataset :

Apple     A

Apple     P

Orange  O

Orange  R

 

Expected output

Apple A,P

Orange O,R

 

Thank

 

 

 

Esteemed Advisor
Posts: 6,636

Re: Top 100 row Not self Typing Sql

After taking some time googling around how this can be done with SQL (the EG Query Builder is just a SQL code generator), I seriously doubt that SAS has a function like MySQL's GROUP_CONCAT in its repertoire. And since other SQL examples (all non-SAS) look quite complicated, I doubt you will be able to do this with simple point-and-click, as they seem to be too hard to handle for the Query Builder interface.

My approach:

data have;
input fruit $ category $;
cards;
Apple A
Apple P
Orange O
Orange R
;
run;

data want (keep=fruit categories);
set have;
by fruit;
length categories $ 100; * use enough length so you don't run out of space;
retain categories;
if first.fruit then categories = '';
categories = catx(',',categories,category);
if last.fruit then output;
run;

proc print data=want noobs;
run;

leads to this result:

fruit     categories

Apple        A,P    
Orange       O,R    
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 197 views
  • 7 likes
  • 4 in conversation