Date:         Mon, 1 Aug 2005 18:01:29 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Inserting data into sas datasets with integrity constrains

On Mon, 1 Aug 2005 12:28:31 -0700, niks <nnkulkarni@GMAIL.COM> wrote:

>Hi,
>Thanks a lot for your reply. I have written some peice of code but not
>sure if I am right as its not working :-) maybe my approach is wong.
>
>What I want to do it:
>
>1. I have 2 tables already created using proc Sql and they have
>integrity constraints like primary and foriegn keys defined on them.

Please run DESCRIBE TABLE statements against these and post the results.

>2. I am reading a .csv file from a folder and I want to insert the data
>which is read from the .csv file into the sas datasets which are
>already created(but are empty initially).
>3. I want to make this as a macro code.

I suggest you get it working in hard code and only then macro-ize.

>4. once the sas dataset is populated then next time the same
>process(this macro should be run bi weekly for getting new data) should
>be followed of reading the data from a new .csv file and inserting data
>into the existing sas dataset. Now this sas dataset should contain the
>history data and the present data.
>5. the problem with my code: I am not able to insert the values read
>from the .csv file dat1.csv into the sas dataset using the macro
>variables.

What does "not able" mean? What are the indications of failure?

>6. Please suggest a work around or how to recode it to make it work if
>the approach is OK.
>
>
>options symbolgen  mlogic merror ;
>%macro sql (d=);
>
>libname temp 'c:\temp';
>filename test 'c:\test';
>
>data t1;
>infile test("&d") dlm = ',' ;
>input name $ country $ city $ pin $ age;
>run;
>
>data _null_;
>call symput("newname",name);
>call symput("newcountry",country);
>call symput("newcity",city);
>call symput("newpin",pin);
>call symput("newage",age);
>run;

THe above step has no SET statement or other incoming data, so the five
DATA step variables are uninitialized numerics (though, curiously, no
messages regarding lack of ininitialization appear in the log) and each
macro variable contains just a dot.

Note that if you do insert

   set t1;

your macro variables will reflect only the last observation; not what you
want.

Fortunately, you really do not have any need to populate a slew of macro
variables.

>
>
>proc sql noprint;
>
>select name, country, city, pin, age from work.t1;
>insert into temp.prod values ("&newname" , "&newcountry", "&newcity",
>"&newpin", "&newage");
>quit;
>
>proc print data=temp.prod;
>run;
>
>%mend sql ;
>%sql (d=dat1.csv) ;