Date:         Tue, 9 Jan 2007 08:18:58 -0800
Reply-To:     "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Subject:      Re: Problem with PROC SQL and MACRO
Comments: To: SAS-L Nirmal <lazybone2k@GMAIL.COM>
Content-Type: text/plain; charset="us-ascii"

Hi,

Is this what you are looking for?


options mprint mlogic symbolgen;
data have;
input Market $ Buyer $ Product $ Type $; cards;
A1 B1 C1 D1
A1 B1 C1 D2
A1 B1 C2 D1
A1 B1 C2 D2
A1 B1 C3 D1
A1 B1 C3 D2
A1 B1 C4 D1
A1 B1 C4 D2
A1 B2 C1 D1
A1 B2 C1 D2
A1 B2 C2 D1
A1 B2 C2 D2
A1 B2 C3 D1
A1 B2 C3 D2
A1 B2 C4 D1
A1 B2 C4 D2
;
run;


proc sql feedback noprint;
select 'compress('||Name||',"ABCD") as '||Name
  into: loop  separated by ','
    from dictionary.columns
     where libname = 'WORK'
      and memname = 'HAVE' ;
quit;


proc sql;
    create table want as
    select &loop
    from have;
quit;




Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing





-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
SAS-L Nirmal
Sent: Tuesday, January 09, 2007 7:45 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Problem with PROC SQL and MACRO

Dear SAS-l users,

I have a dataset with 4 variables and various levels in it. I want to
read the variables and levels and construct a ouput dataset with the
numbers indication the levels. I got lot of suggestion and tips from
SAS-l. I tried little different approach...it is not working. Please
help me in solving the problem. The following is the code i wrote.
*****************************************************
options mprint mlogic symbolgen;
data have;
input Market $ Buyer $ Product $ Type $; cards;
A1 B1 C1 D1
A1 B1 C1 D2
A1 B1 C2 D1
A1 B1 C2 D2
A1 B1 C3 D1
A1 B1 C3 D2
A1 B1 C4 D1
A1 B1 C4 D2
A1 B2 C1 D1
A1 B2 C1 D2
A1 B2 C2 D1
A1 B2 C2 D2
A1 B2 C3 D1
A1 B2 C3 D2
A1 B2 C4 D1
A1 B2 C4 D2
;
run;
proc sql feedback;
select 'do '||name|| ' = 1 to Count( Distinct ' || Name || ' ) As ' ||
Name
  into: loop  separated by ';'
    from dictionary.columns
     where libname = 'WORK'
      and memname = 'HAVE' ;
quit;

data want;
&loop;
output;
end;
end;
end;
end;
run;
************************************************
what i am trying to do is to create a do loop statement through the
into:
clause in PROC SQL. I encounter two different problem, when "select 'do
'||name|| ' = 1 to Count( Distinct ' || Name || ' ) As ' || Name" this
statement executes, **||names||** is resolving into various variables
names, but **Count( Distinct ' || Name || ' ) As ' || Name** is not
resolving. I tried a put statement along with this, then it says that it
need a numeric argument. Please help.
The desired output for the input dataset given in the above sample
should look like this.
************************************************
Market Buyer  Product  Type
1 1 1 1
1 1 1 2
1 1 2 1
1 1 2 2
1 1 3 1
1 1 3 2
1 1 4 1
1 1 4 2
1 2 1 1
1 2 1 2
1 2 2 1
1 2 2 2
1 2 3 1
1 2 3 2
1 2 4 1
1 2 4 2

Please help me. Thanks for all the suggestions and tips.

Regards,
Nirmalkumar