BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASNE
Obsidian | Level 7
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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

17 REPLIES 17
SASNE
Obsidian | Level 7
 
Kurt_Bremser
Super User

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?

SASNE
Obsidian | Level 7

Hi KurtBremer,

 

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

 

 

thanks

Kurt_Bremser
Super User

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.

 

 

Reeza
Super User

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

SASNE
Obsidian | Level 7

Hi Reeza,

 

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

 

as less coding as possible.

 

Thanks

Reeza
Super User

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. 

SASNE
Obsidian | Level 7

Hi Reeza,

 

Yes you are right. Thanks

Reeza
Super User

Fixed it for you ;)

 


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. 

Reeza
Super User

@Kurt_Bremser wrote:

@Reeza wrote:

Fixed it for you 😉

 

 


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

SASNE
Obsidian | Level 7

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

 

 

 

Kurt_Bremser
Super User

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    

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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