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:
>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
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';
>infile test("&d") dlm = ',' ;
>input name $ country $ city $ pin $ age;
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
your macro variables will reflect only the last observation; not what you
Fortunately, you really do not have any need to populate a slew of macro
>proc sql noprint;
>select name, country, city, pin, age from work.t1;
>insert into temp.prod values ("&newname" , "&newcountry", "&newcity",
>proc print data=temp.prod;
>%mend sql ;
>%sql (d=dat1.csv) ;