do loop with %let for fields

Reply
Contributor
Posts: 45

do loop with %let for fields

SAS Enterprise Guide 6.1

 

I need to pull 10 sample observations for each field in a table, and combine the results to 1 field.

 

For example 

Table A has field 1, 2, 3

I want to end up with results that say 

Table A with 2 columns

Column_Name Values

Field 1              sdfs, asdf,erhhs,oishgl2, shgeax, 

Field 2              ashth, hw1234,345,3457778,kjhsdf

Field 3              1577, 12.354, 4597, 123sdf

 

So far I have this written, but I just can't get it to recognize the &Column as an actual field. 

If I take that out and sub &column as value with FiELD_1 (of course using the actual field name) I get exactly what i'm looking for, just the same data for each field... so i need to figure out how to make that a variable.

Any tips?

 

%let system=SYS1;
%let table=TBL1;

PROC SQL;
CREATE TABLE sampledata_raw
(COLUMN char(100) ,
VALUE char(100)
);
QUIT;


%macro obs(lib= ,ds= );
proc sql noerrorstop;
create table &ds as
select *
from &lib..&ds(FASTLOAD=YES obs=10)
; quit;
%mend;

%obs(lib=&system , ds=&table);

proc transpose data=work.&table
out=work.tableTranspose(DROP=_label_);
var _all_;
run;

 


DATA _NULL_;
SET tableTranspose END=END;
IF END THEN DO;
CALL SYMPUT ('TOT_OBS', _N_);
END;
RUN;


%PUT &TOT_OBS;

 

%MACRO DO_IT;

 

DATA WORK.THIS_ONE;
SET tableTranspose;
IF &I = _N_ THEN OUTPUT;
RUN;

DATA _NULL_;
SET THIS_ONE;
CALL SYMPUT ('_NAME_',TRIM(_NAME_));
RUN;

 

%LET Column = %UNQUOTE(%STR(%')&_NAME_%STR(%'));

proc sql;
create table one as
select distinct
&column as Column,
&Column as Value
from &system..&table (obs=10)
;
quit;

 

PROC APPEND BASE = sampledata_raw data= ONE; RUN;


%MEND DO_IT;

 

%MACRO LOOP;
%DO I=1 %TO &TOT_OBS;
%PUT "---------------------------------------------";
%PUT &TOT_OBS LOOPS -> &I;
%PUT "---------------------------------------------";
%DO_IT;
RUN;
%END;
%MEND LOOP;
%LOOP;

 

 

Grand Advisor
Posts: 10,210

Re: do loop with %let for fields

It really helps to show in a bit more detail what your starting data looks like and what the final result should look like.

 

From what I can follow I would probably not be using that complicated of a macro at all. Use Dictionary.columns to find the variables with NPOS in the range (NPOS being the column position in the variables) and then use that information to create the table.

 

Or if this is to be a random sample Proc Surveyselect might come into play.

Contributor
Posts: 45

Re: do loop with %let for fields

 

Original data: 

ANY table - I need a solid 10 values from each field. combined into 1 field.

Table A

Has 

Field 1   Field 2   Field 3

data       data       data

data       data       data

data       data       data

data       data       data

data       data       data

data       data       data

 

I need:

Table A

Column_Name   Sample_Data

Field 1                data, data, data, data, data

Field 2                data, data, data, data, data

Field 3                data, data, data, data, data

 

So I also have this bit of code....

The problem is that it just looks at the first 10 observations and some of the fields may be blank....

I need to deliver 10 sample values for each field.... so, i wanted to pull that separately, combine to 1 table, then transpose and concatenate. 

 

I'm not following what you are suggesting.....

 

 

%let system=SYS1;
%let table=TBL1;


%macro obs(lib= ,ds= );
proc sql noerrorstop;
create table &ds as
select *
from &lib..&ds(FASTLOAD=YES obs=10)
; quit;
%mend;

%obs(lib=&system , ds=&table);

proc transpose data=work.&table
out=work.out1(DROP=_label_);
var _all_;
run;

data out1(drop= col1 col2 col3 col4 col5 col6 col7 col8 col9 col10); set out1;
sample=trim(left(col1))||', '||trim(left(col2))||', '||trim(left(col3))||', '||trim(left(col4))||', '||trim(left(col5))||', '||trim(left(col6))||', '||trim(left(col7))||', '||trim(left(col8))||', '||trim(left(col9))||', '||trim(left(col10));
run;

Respected Advisor
Posts: 4,973

Re: do loop with %let for fields

Well, your final SELECT includes:

 

&Column as Column,

&column as Value

 

No matter what is in &COLUMN, you have to expect that COLUMN and VALUE will be identical.  You could create two macro variables instead of one:  name of the variable, and set of 10 values.  Rather than go that route, I would suggest simplifying the entire process.  Once %OBS creates &DS,  you have a small table to work with.  You can process that as many times as you would like, without running up a bill.  Here would be some code you could add to the end of the definition of %OBS:

 

proc contents data=&ds noprint out=_contents_ (keep=name);

run;

 

data _null_;

set _contents_ end=done;

if _n_=1 then call execute('proc sql;');

call execute ('select distinct ' || name || ' into : '  || name || " separated by ',' from &ds;");

if done then call execute('quit;');

run;

 

data want;

set _contents_;

value = symget(name);

rename name=Column;

run;

 

There are some tricky parts (feel free to ask), and it's untested code.  But you would get there much more directly.  Just running %OBS would give you your output data set.

Contributor
Posts: 45

Re: do loop with %let for fields

yes, i realize that as it is written Column & value are identical- I just couldn't get it to run without errors any other way.... and i wasn't sure how to get there ....

OK, i see what you are suggesting....
The problem is that the initial %obs doesn't have 10 values for each field. 

maybe the ST_CD (state code) for the first 10 obs that it grabs all says SC but really there's any state in the union available as optional sample data... 

or maybe the first 10 obs have half of them blank.... so i really only get 5 obs...

I'm not sure how to make sure i get a good sampling of the data

 

 

That's why i was doing the complicated way.... 

 

 

 

 

 

 

Contributor
Posts: 45

Re: do loop with %let for fields

So, let's say my fields are 

Table: People

Name        Gender        State          Phone

Sue            F                 SC              123-123-4567

Joe            M                 SC              123-987-4561              

Mary          F                  SC              

Jane          F                   FL              345-657-4215

Tom           M                  OH              

Kate          F                   OH              216-654-1248

 

I need:

Table: People

Column_Name           Values

Name                         Sue, Joe, Mary, Jane, Tom, Kate

Gender                      F, M

State                          SC, FL, OH

Phone                        123-123-4567,  123-987-4561, , 345-657-4215, 216-654-1248

 

Grand Advisor
Posts: 9,576

Re: do loop with %let for fields

Here is :



data have;
infile cards truncover;
input (Name        Gender        State          Phone ) (: $20.);
cards;
Sue            F                 SC              123-123-4567
Joe            M                 SC              123-987-4561              
Mary          F                  SC              
Jane          F                   FL              345-657-4215
Tom           M                  OH              
Kate          F                   OH              216-654-1248
;
run;
proc sql noprint;
 select distinct name into : name separated by ',' from have;
 select distinct gender into : gender separated by ',' from have;
 select distinct state into : state separated by ',' from have;
 select distinct phone into : phone separated by ',' from have;
quit;
data want;
length name $ 40 value $ 32767;
name='name';value="&name";output;
name='gender';value="&gender";output;
name='state';value="&state";output;
name='phone';value="&phone";output;
run;




Respected Advisor
Posts: 4,973

Re: do loop with %let for fields

I might recommend the "quick and dirty" way here ... increase your sample table from 10 to 100 observations. 

 

Using SYMGET will create VALUES with a length of $200.  You might have to do a little post-processing and remove some characters at the end.  Any time the length of VALUES is less than 199:  find the last comma in the list.  Remove the last comma and all text that follows.  That way, you get rid of partial values that won't fit within the length of 200.

 

Sometimes you'll get more than 10 sample values, perhaps sometimes you'll get less.  But you'll be in the ballpark.

Contributor
Posts: 45

Re: do loop with %let for fields

Thanks i might just have to give up on this....
my database is millions of records huge - even increasing the initial table to 100 i could still get only SC records as my first 100 obs. 

 

 

I'm so close - i just need to figure out how to see that variable as a field....

Respected Advisor
Posts: 4,973

Re: do loop with %let for fields

Here's a recommendation, then.  Extract the entire table, then take a sample:

 

data sample;

do _n_=1 to _nobs_ by 100;

   set entire_database nobs=_nobs_ point=_n_;

   output;

end;

stop;

run;

 

By taking every 100th observation, you should get more diversity in your data values, but a small enough data set that you can work with it to extract the distinct values.

Contributor
Posts: 45

Re: do loop with %let for fields

sounds like it could work - but my table has about a million records.... IT won't let me use that kind of resources.... lol.

Grand Advisor
Posts: 10,210

Re: do loop with %let for fields

proc surveyselect data=<yourtablenamehere> out=sampledatasetname

      sampsize=100;

run;

 

Randomly selects 100 records from you dataset. There will be a couple of added variables related to selection probability and weighting that you could drop/ignore in further processing.

Grand Advisor
Posts: 9,576

Re: do loop with %let for fields

You gotta know the most length of character variable is 32767. If you have big table, 32767 can't hold all these value for you in a single variable .

 

proc transpose data=have out=temp;

 var _all_;

run;

 

data want;

 set temp;

 length want $ 32767 ;

 want=catx(.........);

run;

Contributor
Posts: 45

Re: do loop with %let for fields

Thanks.
The biggest field in this particular table is 15 characters. I want 10 values for each field. at most it would be 150 (plus 9 commas and 9 spaces so 168 characters).

 

A Teammate suggested this - but it does not give me 10 unique sample values for each field. 

 

 

Contributor
Posts: 45

Re: do loop with %let for fields

One part of the solution:

 

%LET Column = %UNQUOTE(%STR(%')&_NAME_%STR(%'));
%LET Value = &_NAME_;


proc sql;
create table one as
select distinct
&column as Column,
&Value as Value
from &system..&table (obs=10)
;
quit;

 

Last part I'm still looking to solve for  - I'm losing about 50 fields due to format not matching.... 

Ask a Question
Discussion stats
  • 18 replies
  • 668 views
  • 0 likes
  • 4 in conversation