Table reference in Do Loops?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 86
Accepted Solution

Table reference in Do Loops?

Hello Forum,

 

I'm a SAS Enterprise Guide 7.1 user & have been looking at possibilities of looping through tables in a SAS EG Project.

 

I want to write my code in a New Program in EG to do this.

 

If I wanted to loop through one Column of a table, capture that Rows value for all Rows & then output all values with a comma separater, how can I do this?

 

I've tried 'Do ... Loops' but they don't seem to refer to any table so I am confused as to how it could perform any action on a defined table?

 

Excuse my ignorance, I have had a good look at this and I don't yet understand how this all works.

 

Cheers

 

 

 


Accepted Solutions
Solution
‎05-28-2016 01:24 AM
Super User
Posts: 22,480

Re: Table reference in Do Loops?

Posted in reply to Oscarboots

The method used depends on what you plan on doing with it in the future. 

 

@KurtBremser Is likely the answer you want in the long run, but as for your original question, SAS loops through rows automatically so you don't need a do loop in a data step. 

 

Data want;
Length name_cat $256.;
Set sashelp.class;
Retain name_cat;

Name_cat=catx(', ', name_cat, name);
Run;

SAS interfaces are either Base, EG and most recently SAS Studio. You can program in all three, a specific test environment isn't present by default though your company may have one. If you want a specific learning environment look into SAS University Edition. 

 

View solution in original post


All Replies
Super User
Posts: 9,292

Re: Table reference in Do Loops?

Posted in reply to Oscarboots

Look at proc sql and a select ... into:variable separated by ','

variable denotes a newly created macvo variable.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 86

Re: Table reference in Do Loops?

Posted in reply to KurtBremser

Thanks KurtBremser,

 

I need a basic example if you could help me until I follow what you mean.

 

If I had a Table with 100 Rows of results in EG called Sales with 3 fields - 1. ID (Int), 2. CustomerName (Varchar), 3. SalesTotal (Int).

 

It sits in a SAS Database 'CRM'.

 

How could I write all Customers Names witha Comma & output the results?

 

Thanks

Super User
Posts: 9,292

Re: Table reference in Do Loops?

Posted in reply to Oscarboots
proc sql;
select
  CustomerName into : customernames separated by ','
from crm.table;
quit;

%put &customernames;

will put the list of names into the log.

If you want it as output, add

data _null_;
file print;
put "&customernames";
run;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 86

Re: Table reference in Do Loops?

Posted in reply to KurtBremser

Thanks again!

 

Can you advise on a couple of other syntax parts please?

 

What/Why id 'Data Null' used instead of 'Data Tablename'  (where tablename is what I want call the output)

 

What is the meaning & purpose of the '%' before a Function?

 

Your knowledge is much appreciated!

 

 

Super User
Posts: 9,292

Re: Table reference in Do Loops?

Posted in reply to Oscarboots
Frequent Contributor
Posts: 86

Re: Table reference in Do Loops?

Posted in reply to KurtBremser

Thanks I'll try these out, is there an Integrated Design Environment (IDE) that I can use to run and test this code?

 

I'm not aware of any SAS 'Test' IDE.

 

Cheers Peter

Super User
Posts: 9,292

Re: Table reference in Do Loops?

Posted in reply to Oscarboots

You don't need a test environment, as my code does not change any data. Enter the code (with appropriate adapations reg. dataset names etc) in a program window in EG and run it.

Your SAS admin should already have taken precautions against accidental overwriting of sensitive data.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎05-28-2016 01:24 AM
Super User
Posts: 22,480

Re: Table reference in Do Loops?

Posted in reply to Oscarboots

The method used depends on what you plan on doing with it in the future. 

 

@KurtBremser Is likely the answer you want in the long run, but as for your original question, SAS loops through rows automatically so you don't need a do loop in a data step. 

 

Data want;
Length name_cat $256.;
Set sashelp.class;
Retain name_cat;

Name_cat=catx(', ', name_cat, name);
Run;

SAS interfaces are either Base, EG and most recently SAS Studio. You can program in all three, a specific test environment isn't present by default though your company may have one. If you want a specific learning environment look into SAS University Edition. 

 

Frequent Contributor
Posts: 86

Re: Table reference in Do Loops?

Thanks Reeza & KurtBremser,

 

I don't always have access to my Company system & like to test my questions on my own Laptop.

 

I'm not aware of any SAS products that allow me access & I think it would help SAS become more successful if a User could test code in a trial program environment.

 

This what I need now & it may cause Users who really like to recommend SAS to have no choice but use another product.

 

I think SAS is the best but this limitation can sometimes lose opportunties when selecting the software for a company.

 

Your competition offers trial access, why doesn't SAS?

 

I'm not having a go at you, I want SAS to succeed.

 

Thanks

Super User
Posts: 9,292

Re: Table reference in Do Loops?

Posted in reply to Oscarboots

SAS University Edition provides a free testbed.

If testing anytime/anywhere is of such importance, have your company obtain a desktop license, or have a remote home office (via VPN) set up with access to the SAS server. That's what I have.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 22,480

Re: Table reference in Do Loops?

Posted in reply to Oscarboots

That's what SAS University Edition is, out for about 2 years now. 

 

You can download and run locally or set up on AWS. 

 

This is significantly better than a trial version since the restriction is only on usage - learning purposes only. There's no time limit like other trial programs such as SPSS. I think SAS is leading the way in this manner. 

 

 http://www.sas.com/en_us/learn/analytics-u.html

 

Additinally there are two free e learning courses available, one for programming and one for statistics. See the Analytics U section of the community under Learning. 

Frequent Contributor
Posts: 86

Re: Table reference in Do Loops?

Thanks Reeza,

 

All I had read in forums etc was that there wasn't a test product, Thanks to both of you for clarifying.

Super User
Super User
Posts: 7,736

Re: Table reference in Do Loops?

Posted in reply to Oscarboots

Sounds like you want to create a CSV file from your data?  So each observation will become one row in the text file. Each variable will become one column of the data in the text file.

 

You can use PROC EXPORT to make a CSV file from a dataset.  I am sure that Enterprise Guide has a way to do that easily.

 

If you want to generate your own it is really easy in SAS.  Especially if you are not concerned about outputting the column headers.  To write the data from the table MYLIB.MYDSN to the file 'myfile.csv' you could use this simple data step.

 

data _null_;
   set mylib.mydsn ;
   file 'myfile.csv' dsd ;
   put (_all_) (+0) ;
run;
Frequent Contributor
Posts: 86

Re: Table reference in Do Loops?

Thanks to all for your responses.

 

Apologies for the delayed response but I had some issues with getting access to the SAS Studio (Which is great!).

 

I have a question on this tool though.

 

How do I upload my data into it to run queries?

 

Thanks again!!  :  >

🔒 This topic is solved and locked.

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

Discussion stats
  • 19 replies
  • 680 views
  • 5 likes
  • 5 in conversation