SAS Support Communities

sasmaverick · ‎01-18-2023

@Ksharp Thanks a lot. This is exactly what I was looking for!!

sasmaverick · ‎01-17-2023

I mean is there a way to do it in a single step (like proc surveyselect). I can surely think of doing it in multiple steps. 1. Select unique IDS from dataset A into dataset B 2. Pick random sample (x%) from dataset B into dataset C 3. Inner join dataset C to dataset A

sasmaverick · ‎01-17-2023

I have a dataset with 10,000 rows and 1000 unique account ids. I need to select 300 random account ids and return all rows for those 300 ids (without specifying the ids in a where clause). How do I do it? Please see the below example: Input dataset account_id Amount ABC 100 ABC 150 ABC 200 DEF 90 DEF 80 Output dataset e.g., If I select 1 random account id from the above data, I expect the following result: account_id Amount DEF 90 DEF 80 @Reeza @Tom @Ksharp Thanks a bunch for the help!

sasmaverick · ‎06-16-2022

Yes I did refresh the TD library in SAS. I have Teradata SQL Assistant. When I use the below code in that, it's able to create the volatile table create volatile table temp as ( select top 10 cust_id from teralib.customers ) with data primary index (cust_id) on commit preserve rows However, when I run the same code below in SAS, I don't see the table. All I am looking for is the colatile table to be brought into a SAS library. proc sql; CONNECT TO ODBC AS TDP5 (DSN="TDP5DBC" UID="&SGB_UID." PWD="&SGB_PWD." connection=global); execute( create volatile table temp as ( select top 10 cust_id from teralib.customers ) with data primary index (cust_id) on commit preserve rows ) by TDP5; quit;

sasmaverick · ‎06-15-2022

I am using the below code: /*create volatile table*/ libname td odbc dsn = "TDP5DBC" uid = &my_uid. pwd = &my_pwd. connection=global dbmstemp=yes; proc sql; connect using td; execute by td(create volatile table temp as ( select top 10 * from teralib.customers ) with data primary index (cust_id) on commit preserve rows); quit; However, still nothing seems to be created in TD:

sasmaverick · ‎06-15-2022

I think I was able to create the volatile table using the below syntax. Thanks to @SASKiwi for the inspiration. However, I am not sure how to access this table in SAS now? /*create volatile table*/ proc sql; CONNECT TO ODBC AS TDP5 (DSN="TDP5DBC" UID="&NT_UID." PWD="&NT_PWD." ); execute( create volatile table temp as ( select top 10 * from teralib.customers ) with data primary index (Cust_ID) on commit preserve rows ) by TDP5; quit;

sasmaverick · ‎06-15-2022

I have an extremely large SAS dataset (15mn rows) that I want to create as a table in Teradata. I do not have SAS/ACCESS Interface to Teradata, hence trying to use the SAS/ACCESS Interface to ODBC. The problem is it takes an extremely long time to create table in Teradata using the ODBC approach. What is the workaround?

sasmaverick · ‎06-14-2022

Hello, I am trying to create volatile table in Teradata using ODBC as I do not have license to access the Teradata engine. Below is the Teradata SQL query I am using in SAS: CREATE VOLATILE TABLE base_acct_extract ( account_id varchar(30) ) PRIMARY INDEX(account_id) ON COMMIT PRESERVE ROWS; INSERT INTO base_acct_extract VALUES ('ABC1200'); Can I even use the ODBC engine to create a volatile table? If so, how do I create and access the table? @Reeza @Ksharp Thanks, NT

sasmaverick · ‎01-04-2018

Hi @RW9 Thanks for the effort. I think this works. The customer count in output data shown was just for reference and not exact numbers that we would get. What I am trying to achieve is to get the count of customers unique across an amount-channel-brand group. and I think your solutions addresses that. Regards,

sasmaverick · ‎01-04-2018

@RW9: Sorry. Below my restructured question. It's a bit challenging for me hence I was looking for a proper code: data have; infile datalines; input CUSTOMER_ID : $1. Channel_R : $15. Channel_O : $15. Channel_W : $15. Brand_Brand1 : $15. Brand_Brand2 : $15.; datalines; 1 Missing $0.01-$25 $100.01-$150 $150.01-$200 $200+ 2 <$0 $25.01-$50 Missing $100.01-$150 $50.01-$75 3 Missing $0.01-$25 $100.01-$150 $150.01-$200 $200+ 4 <$0 $25.01-$50 Missing $100.01-$150 $50.01-$75 5 Missing $0.01-$25 $100.01-$150 $150.01-$200 $200+ 6 <$0 $25.01-$50 Missing $100.01-$150 $50.01-$75 7 $25.01-$50 $0.01-$25 $100.01-$150 $150.01-$200 $200+ 8 <$0 $25.01-$50 $25.01-$50 $100.01-$150 $50.01-$75 9 Missing $0.01-$25 $100.01-$150 $150.01-$200 $200+ 10 <$0 $25.01-$50 Missing $100.01-$150 $50.01-$75 ; run; data want; infile datalines; input Amount : $15. Customer_Count : $5. Brand : $15. Channel : $10.; datalines; Missing 100 Brand1 W <$0 200 Brand1 W $.01-$25 300 Brand1 W $25.01-$50 400 Brand1 W $50.01-$75 500 Brand1 W $75.01-$100 600 Brand1 W $100.01-$150 700 Brand1 W $150.01-$200 800 Brand1 W $200+ 900 Brand1 W Missing 100 Brand2 R <$0 200 Brand2 R $0.01-$25 300 Brand2 R $25.01-$50 400 Brand2 R $50.01-$75 500 Brand2 R $75.01-$100 600 Brand2 R $100.01-$150 700 Brand2 R $150.01-$200 800 Brand2 R $200+ 900 Brand2 R ; run;

sasmaverick · ‎01-04-2018

I have the below dataset at a customer level- data have; infile datalines; input CUSTOMER_ID : $1. Channel_R : $15. Channel_O : $15. Channel_W : $15. Brand_Brand1 : $15. Brand_Brand2 : $15.; datalines; 1 Missing $0.01-$25 $100.01-$150 $150.01-$200 $200+ 2 <$0 $25.01-$50 Missing $100.01-$150 $50.01-$75 3 Missing $0.01-$25 $100.01-$150 $150.01-$200 $200+ 4 <$0 $25.01-$50 Missing $100.01-$150 $50.01-$75 5 Missing $0.01-$25 $100.01-$150 $150.01-$200 $200+ 6 <$0 $25.01-$50 Missing $100.01-$150 $50.01-$75 7 $25.01-$50 $0.01-$25 $100.01-$150 $150.01-$200 $200+ 8 <$0 $25.01-$50 $25.01-$50 $100.01-$150 $50.01-$75 9 Missing $0.01-$25 $100.01-$150 $150.01-$200 $200+ 10 <$0 $25.01-$50 Missing $100.01-$150 $50.01-$75 ; run; I need the output as follows- data want; infile datalines; input Amount : $15. Customer_Count : $5. Brand : $15. Channel : $10.; datalines; Missing 100 Brand1 W <$0 200 Brand1 W $.01-$25 300 Brand1 W $25.01-$50 400 Brand1 W $50.01-$75 500 Brand1 W $75.01-$100 600 Brand1 W $100.01-$150 700 Brand1 W $150.01-$200 800 Brand1 W $200+ 900 Brand1 W Missing 100 Brand2 R <$0 200 Brand2 R $0.01-$25 300 Brand2 R $25.01-$50 400 Brand2 R $50.01-$75 500 Brand2 R $75.01-$100 600 Brand2 R $100.01-$150 700 Brand2 R $150.01-$200 800 Brand2 R $200+ 900 Brand2 R ; run; I tried using PROC Transpose but since the dataset is very large, it takes a huge amount of time..

sasmaverick · ‎12-29-2017

Thanks a lot. Works like a charm.

sasmaverick · ‎12-28-2017

Below is the dataset I have What I have Customer ID Ord_Month Channel Brand 1 Flag Brand 2 Flag Transaction Flag Amount 1 2016-02 Web 1 0 1 100 1 2016-02 Retail 0 1 1 200 2 2016-03 Web 1 1 1 50 2 2016-04 Others 0 1 1 90 2 2016-04 Others 0 1 1 300 I want to summarize the amount, transactions and customers by brand and channel. Transaction is valid if transaction flag is 1. Below is the output I expect What I want Fiscal Period Total Amount Total Customers Total Transactions Brand Channel 2016-02 100 1 1 Brand 1 Web 2016-02 200 1 1 Brand 2 Retail 2016-03 50 1 1 Both Web 2016-04 390 1 2 Brand 2 Others For each distinct combination of Brand and Channel for a given fiscal period I want to calculate the total customers (count of customer ID), total transactions (count of transaction flag) and total amount (sum of amount). Please help.

sasmaverick · ‎06-07-2017

Are you sure this is working. I still don't see 11-15 range in the report. ##- Please type your reply above this line. Simple formatting, no attachments. -##

sasmaverick · ‎06-07-2017

This doesn't give me percentages. ##- Please type your reply above this line. Simple formatting, no attachments. -##

Online Status	Offline
Date Last Visited	‎01-19-2023 12:48 AM

SAS Support Communities

Re: Select Subset of a Group and Return All Rows

Re: Select Subset of a Group and Return All Rows

Select Subset of a Group and Return All Rows

Re: Create Volatile Table in Teradata Using ODBC

Re: Create Volatile Table in Teradata Using ODBC

Re: Create Volatile Table in Teradata Using ODBC

Unable to Transfer Large SAS Dataset to Teradata Using ODBC

Create Volatile Table in Teradata Using ODBC

Re: Restructure Dataset

Re: Restructure Dataset

Re: Select Subset of a Group and Return All Rows

Re: Restructure Dataset

Re: Summarize by Multiple Variables in Data Step

Re: Month-Year Loop

Re: Country Code Format Lookup

Re: Create Volatile Table in Teradata Using ODBC

Re: Select Subset of a Group and Return All Rows

Re: Select Subset of a Group and Return All Rows

Select Subset of a Group and Return All Rows

Re: Create Volatile Table in Teradata Using ODBC

Re: Create Volatile Table in Teradata Using ODBC

Re: Create Volatile Table in Teradata Using ODBC

Unable to Transfer Large SAS Dataset to Teradata Using ODBC

Create Volatile Table in Teradata Using ODBC

Re: Restructure Dataset

Re: Restructure Dataset

Restructure Dataset

Re: Summarize by Multiple Variables in Data Step

Summarize by Multiple Variables in Data Step

Re: Show All Ranges of Proc Format in Proc Freq

Re: Show All Ranges of Proc Format in Proc Freq

Follow Us

What is...