topic Re: Top 100 row Not self Typing Sql in SAS Enterprise Guide

Top 100 row Not self Typing Sql

SASNE — Tue, 14 Feb 2017 11:35:51 GMT

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

Re: Top 100 row Not self Typing Sql

SASNE — Tue, 14 Feb 2017 11:36:09 GMT

Re: Top 100 row Not self Typing Sql

Kurt_Bremser — Tue, 14 Feb 2017 11:50:44 GMT

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?

Re: Top 100 row Not self Typing Sql

SASNE — Tue, 14 Feb 2017 12:48:23 GMT

Hi KurtBremer,

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

thanks

Re: Top 100 row Not self Typing Sql

Kurt_Bremser — Tue, 14 Feb 2017 12:52:33 GMT

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.

Re: Top 100 row Not self Typing Sql

Reeza — Tue, 14 Feb 2017 12:56:24 GMT

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

Re: Top 100 row Not self Typing Sql

SASNE — Tue, 14 Feb 2017 13:02:09 GMT

Hi Reeza,

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

as less coding as possible.

Thanks

Re: Top 100 row Not self Typing Sql

Reeza — Tue, 14 Feb 2017 13:20:47 GMT

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.

Re: Top 100 row Not self Typing Sql

Kurt_Bremser — Tue, 14 Feb 2017 13:24:45 GMT

@SASNE wrote:

Hi Reeza,

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

as less coding as possible.

Thanks

You can limit the number of observations in the output in the Enterprise Guide Query Builder. So you would need to create queries for both tables that limit to 1000 obs, and then concatenate them with Append Data.

Or you could type five lines of code and be finished in a minute.

Not wanting to work with SAS code means not really wanting to work with SAS at all, because you miss out on 95% of the power of the SAS system.

Stop being a button-pusher and become a SAS User.

Re: Top 100 row Not self Typing Sql

SASNE — Tue, 14 Feb 2017 13:41:13 GMT

Hi Reeza,

Yes you are right. Thanks

Re: Top 100 row Not self Typing Sql

SASNE — Tue, 14 Feb 2017 13:33:48 GMT

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

Re: Top 100 row Not self Typing Sql

Kurt_Bremser — Tue, 14 Feb 2017 13:35:05 GMT

@SASNE wrote:

Hi Reeza,

Yes you are right. Thanks man

You might run into trouble 😉

Re: Top 100 row Not self Typing Sql

Reeza — Tue, 14 Feb 2017 13:46:43 GMT

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.

Re: Top 100 row Not self Typing Sql

Kurt_Bremser — Tue, 14 Feb 2017 13:51:21 GMT

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

Re: Top 100 row Not self Typing Sql

Kurt_Bremser — Tue, 14 Feb 2017 13:52:24 GMT

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

Re: Top 100 row Not self Typing Sql

Reeza — Tue, 14 Feb 2017 13:57:57 GMT

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

Re: Top 100 row Not self Typing Sql

TomKari — Tue, 14 Feb 2017 14:55:58 GMT

Here's an option:

Assuming your variables are Fruit and Letter:

1. Sort by Fruit;
2. Use the "Data" | "Transpose..." task:
   - Letter as the Transpose variable
   - Fruit as Group by
3. Use a Query Builder task:
   - new "Computed Column" | "Advanced Expression"
   - catx(",", Column1, Column2)

Re: Top 100 row Not self Typing Sql

SASNE — Thu, 16 Feb 2017 07:52:22 GMT

Yes it works, Thanks Tom