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
Grand Advisor
Posts: 17,360

Re: Table reference in Do Loops?

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
Esteemed Advisor
Posts: 6,669

Re: Table reference in Do Loops?

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

variable denotes a newly created macvo variable.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 86

Re: Table reference in Do Loops?

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

Esteemed Advisor
Posts: 6,669

Re: Table reference in Do Loops?

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
Frequent Contributor
Posts: 86

Re: Table reference in Do Loops?

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!

 

 

Esteemed Advisor
Posts: 6,669

Re: Table reference in Do Loops?

See here:

SAS Language Reference, DATA Statement

and here:

SAS Macro Language: Reference, Getting Started with the Macro Facility

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 86

Re: Table reference in Do Loops?

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

Esteemed Advisor
Posts: 6,669

Re: Table reference in Do Loops?

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
Solution
‎05-28-2016 01:24 AM
Grand Advisor
Posts: 17,360

Re: Table reference in Do Loops?

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

Esteemed Advisor
Posts: 6,669

Re: Table reference in Do Loops?

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
Grand Advisor
Posts: 17,360

Re: Table reference in Do Loops?

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: 6,351

Re: Table reference in Do Loops?

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.

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

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