About Armand

Armand · ‎01-27-2015

hello i am trying to connect to a database via db2. the following is my code and store the result into MYWORK : rsubmit; libname mywork 'h:\user\my document\myfile'; %let mydbpasswd=%str({sasenc}12222222222221); proc sql; connect to db2 (dsn="table" user=ismine pass=&mydbpasswd"); create table mywork.status as select * from connection to db2 (select prod_id, prod_name,status from table1); disconect from db2; quit; endrsubmit; The log reproduced my code and and finish by "Remote submit to SASGRID complete", I cannot find mywork library i created neither a data( dataset Status). Any help? Thank you in advance

Armand · ‎01-23-2015

Sorry, long day. it did work with Excel. Thanks a lot.

Armand · ‎01-23-2015

I could not apply the hot fix. So i tried the last option and i go this error message: Failed to connect to the Server Is there a way we work around the idea in Post 6? thanks!

Armand · ‎01-23-2015

the csv works but i am getting only the last data set (Test2). And one i take out the "Replace", only the first data set is exported. How to get all of them in one workbook?

Armand · ‎01-23-2015

Excel 2010 and SAS 9.3 The first data set 36440 (6 variables) rows and the two remaining data set are 1 row

Armand · ‎01-23-2015

it's still showing the same error message.

Armand · ‎01-23-2015

I have used the code below to export 3 data set in excel: %macro export(dst=); proc export data=mywork.&dst outfile="h:\My Documents\MyFile\SAS\MyWork\Result.XLS" Label dbms=xls Replace; sheet=" "; run; %mend; %export(dst=Comparison); %export(dst=Test1); %export(dst=Test2); The log summarize successfull but i am unable to open the file Result.xls from my directory. When i try to open it : "Excel found unreadable content in Result.xls.Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes." I clicked yes but it does not open. Any idea? Thank you for your help

Armand · ‎01-23-2015

PGStats with your suggestion, i am getting different result meaning i am getting more rows than expected. The table Production_before has 36200 rows and Product_after has 36440. When i used your code i got 65745 rows in the result data. I will stick with my response from the post 4.

Armand · ‎01-22-2015

thank you much RW9 and Ballardw. Both suggestions get me the right result ... distinct and adding city to the join

Armand · ‎01-22-2015

thanks for replying, here is what im getting by adding distinct : Product_id Product _name City Market(Million) Market_test 111 Hewlett Parkard New York 50-60 match 111 Hewlett Parkard New York 50-60 no match 111 Hewlett Parkard San Francisco >60 match 111 Hewlett Parkard San Francisco >60 no match 111 Hewlett Parkard Miami 20-30 match 111 Hewlett Parkard Miami 20-30 no match 111 Hewlett Parkard New Jersey 40-50 match 111 Hewlett Parkard New Jersey 40-50 no match this is what i am expecting : Product_id Product _name City Market(Million) Market_test 111 Hewlett Parkard New York 50-60 match 111 Hewlett Parkard San Francisco >60 match 111 Hewlett Parkard Miami 20-30 match 111 Hewlett Parkard New Jersey 40-50 match 111 Hewlett Parkard Houston < 10% of State Market match . . . . . . . . . . . . . . . . . . . . Any idea?

Armand · ‎01-22-2015

hello I have two samples tables below : Production_Before and Production_After and data in the table should look the same base on the test i am conducting. I am trying to verify that the data entry is correct. Product_id Product _name Category Country City Market(Million) 111 Hewlett Parkard Computer USA New York 50-60 111 Hewlett Parkard Computer USA San Francisco >60 111 Hewlett Parkard Computer USA Miami 20-30 111 Hewlett Parkard Computer USA New Jersey 40-50 111 Hewlett Parkard Computer USA Houston < 10% of State Market 111 Hewlett Parkard Computer GB Birmingham < 5% of GB Market 111 Hewlett Parkard Computer GB Bradford >6 111 Hewlett Parkard Computer GB London ❤️ 111 Hewlett Parkard Computer GB Cambridge <2 111 Hewlett Parkard Computer GB Manchester >50 111 Hewlett Parkard Computer Nigeria Lagos <2X 111 Hewlett Parkard Computer Germany Berlin 50-60 111 Hewlett Parkard Computer Germany Hambourg 40-50 111 Hewlett Parkard Computer Germany Munich Very concentrated 111 Hewlett Parkard Computer Germany Cologne <20 111 Hewlett Parkard Computer Ghana Accra Not Meaningful 111 Hewlett Parkard Computer Tunisia Tunis NA and i use the code below to compare the entry proc sql; create table mywork.comparison as select a.product_id, a.product_name, case when a.market=b.market then 'match' else 'no match' end as Market_test From mywork.Production_after a left join mywork.Production_before b where a.Product_id=b.Product_id; The result of my code is multiplying the value of the fields (City and Market) in Look up like this: Product_id Product _name City Market(Million) 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard New York 50-60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 111 Hewlett Parkard San Francisco >60 what am i doing wrong? Any help? thank you

Armand · ‎01-19-2015

thanks Reeza it works

Armand · ‎01-19-2015

So yes that s the right row count you have in the post. Now the question is, where the problem is come from because i have used left join multiple time in my codes and got the expected results. So i am still looking at other ways beside left join to get the result.

Armand · ‎01-16-2015

yeah that is what im trying to find out. the result is too much that it cannot even display (load) so i can check where the extra data coming from.

Armand · ‎01-16-2015

the pdf suggested has good stuff but that s how i did. I thought there is macro way to do that when you have multiple excel files to import from external.

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Re: Match table 1 and 2

Re: Match table 1 and 2

Re: Match table 1 and 2

Re: Match table 1 and 2

Re: Match table 1 and 2

Re: Match table 1 and 2

Re: Match table 1 and 2

Match table 1 and 2

Re: How to merge two tables

Re: How to merge two tables

Re: How to merge two tables

db2 connection in sas pc

Re: Export multiple data set from SAS in Excel

Re: Export multiple data set from SAS in Excel

Re: Export multiple data set from SAS in Excel

Re: Export multiple data set from SAS in Excel

Re: Export multiple data set from SAS in Excel

Export multiple data set from SAS in Excel

Re: Help with look up with repeated id

Re: Help with look up with repeated id

Re: Help with look up with repeated id

Help with look up with repeated id

Re: How to create a macro to import multiples workbooks with different...

Re: help with :ERROR: Expression using equals (=) has components that ...

Re: help with :ERROR: Expression using equals (=) has components that ...

Re: How to create a macro to import multiples workbooks with different...