About Liej

Liej · ‎09-02-2014

Thanks

Liej · ‎09-02-2014

If I add the create index key I get this result Table_result Key 125634 159631 178963 345678 456789

Liej · ‎09-02-2014

Hi, I was wondering if it is possible to create a table based upon an other table and add an index immediatly? basic Table_test Naam Key Blabla Boemboem Jos 125634 Dq Sddsf Jos 125634 Cxc Ffdfd Maie 159631 Dsdjk Jkhjk Marius 178963 Sdsd Ssds Aline 345678 Dsdsdsdi Ioyoyo Aline 345678 Sdsds Sdsdsd Marc 456789 Szaéz Azaz Marc 456789 Sdsi uuii Result I would like to have. Table_result Id Key 1 125634 2 159631 3 178963 4 345678 5 456789 PROC SQL; CREATE TABLE Table_result AS SELECT DISTINCT t1.Key FROM Table_test t1; QUIT;

Liej · ‎05-17-2013

I used this to add the '-signs %let Excel= %str(%'&path/Excel/&&E&i._Excel%'); but then I get this error when using it in ODS listing close; ODS html3 body=&Excel; ERROR 22-322: Syntax error, expecting one of the following: a name, a suoted string Thanks Patrick. %let Excel=&path/Excel/&&E&i._Excel; ods html3 body="&Excel"; These two little changes made it work. Thanks a lot.

Liej · ‎05-17-2013

Thanks for the help. This is my code and it works fine. ODS Listing close; ODS html3 body='/sas/prd/data/2013/Excel/CIT.xls' style=statdoc; /*(1)*/ ODS noresults; title c=brown bold "titel 1"; title2 "titel2"; title3 "title3"; data _null_; set RVOut.CIT; file print ODS=(Var=(a b c d e f)); put a b c d e f; run; ODS html3 close; ODS listing; ODS results; BUT (1) ODS html3 body='&Excel' style=statdoc; doesn't work anyone who can help me to add the '-sign before and after the &Excel variable?

Liej · ‎05-16-2013

I'm trying the ods.tagset but I can't find a way to create such an excel. Anyone who can help me? Liej

Liej · ‎04-25-2013

I was wondering if it's possible to create an excelfile with this on top in the sheet &&B&i._Naam 2013-03.xls Ontheffing Roerende Voorheffing - &&B&i._Naam Datum Beslissing : 22/03/2013 followed with a dataset or if there is no data in the dataset "nihil". V1 V2 V3 V4 V5 V6 V7 V8 V9 dataset or nihil Hope someone can help me. Liej

Liej · ‎04-18-2013

I changed my bank-file in a way I'm now able to spit the RV-file. Dataset BankList: Bank Naam Rek1 Taal B1 AA1 GB44CITI26408067654932 EN B2 AA2 NL32FTSB1387056271 EN B3 BB1 007-2567914-12 FR B4 BB2 001-0077540-70 FR B5 CC 301-1234567-89 NL B6 DD NL65DEUT1234567890 EN Instead of using Rek2 I added a line with the second Rek. I had to change the Naam and added a number. After my program I get 6 datasets AA1 - AA2 - BB1 - BB2 - CC - DD ( in this example) Then I'm wrote a proc datasets to append each datasets2 to the 1. Proc datasets; append base=Lib.AA1 data =Lib.AA2 force; run; Can I write something to do this automatically? So I would like to add a program step in my project that appends the 2-datasets to the 1's. But AA1 append AA2 should become AA-dataset. I would like to do this in a program because sometimes a bank gets a second Rek and then I don't need to change my project. Can this be done in a program Who v

Liej · ‎04-09-2013

ERROR: Argument length greater than length of key variable BBAN at line 29 column 9. ERROR: Keys and data must be specified in the same order and have the same types as given in DefineKey/DefineData at line 29 column 9. ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase. -> 29 do rc=bk.find(key:rek2) by 0 while (rc=0); I guess this is because not every bank has a Rek2. If every bank had only Rek1 or both Rek1 and Rek2 then I had no problem. But it's because some banks don't have a Rek2 I'm dealing with this problem.

Liej · ‎04-08-2013

Hi AhmedAl-Attar, I ones used the Hash fuction. But then I splitted the file based on 1 number in that file. Now I have to check if the bank has 2 numbers (BBAN - BBAN / IBAN - IBAN / BBAN - IBAN) or not. Then search all these records and write them as one new dataset with the bank name. The problem is that not all records has 2 number and that the combinations aren't fixed.

Liej · ‎04-08-2013

I'm trying to split a dataset in mutliple datasets based on macrovariables. How should it work: I have an Excel-file RV.xls with the following description Dataset RV: Type Decision_date Decision_number Amount_in_EURO BBAN IBAN BIC Account_in_the_name_of Beneficiary Notification Payable_day 2 20130322 303187 2425063 007-2567914-12 xxx Jos … 20090417 2 20130322 303188 2441577 001-0987654-32 yyy Pol … 20090417 2 20130322 303189 33000 GB44CITI26408067654932 DRESDEFF zzz Frans … 20091204 2 20130322 303190 66800 001-0077540-69 xyz Alain … 20091204 2 20130322 303191 22200 NL65DEUT1234567890 FTSBNL2R zyx Fritz … 20091204 2 20130322 303192 3000 NL32FTSB1387056270 ABNANL2A xzx Emma … 20091204 2 20130322 303193 700 001-0987654-32 yxy Lucy … 20091205 and a second Excel-file Bank.xls like this. Dataset BankList: Bank Naam Rek1 Rek2 Taal B1 Test1 GB44CITI26408067654932 NL32FTSB1387056270 EN B2 Test2 007-2567914-12 001-0077540-69 FR B3 Test3 301-1234567-89 NL B4 Test4 NL65DEUT1234567890 EN I can read both file and make macrovariables from the Bank-file. data _null_; set RvIn.BankList; if not missing(Naam) then call symput(strip(Bank) || '_Naam', Naam); if not missing(Rek1) then call symput(strip(Bank) || '_Rek1', Rek1); if not missing(Rek2) then call symput(strip(Bank) || '_Rek2', Rek2); if not missing(Taal) then call symput(strip(Bank) || '_Taal', Taal); run; Then I'm counting the records in my BankList-file proc sql noprint; select count(Bank) into :n from RvIn.BankList; /* %put aantal: &n; */ quit; After this I would like to split the dataset RV in multiple set based on the data from BankList. So I would like to make for every Bank a single set IF it occurs offcourse. In this example I would make 3 new datasets Test1 with 2 records / Test2 with 1 record and Test4 also with 1. %macro Split_DataSets; %do i=1 %to &n; proc sql; create table &&B&i._Naam as select * from RvIn.RV as RV where RV.IBAN IN ("&&B&i._Rek1","coalesce(&&B&i._Rek2,0)") or RV.BBAN IN ("&&B&i._Rek1","coalesce(&&B&i._Rek2,0)"); quit; %end; %mend; %Split_DataSets; And that doesn't work. I need to find some routine to make these sets but the problem is that I don't know wether they're using IBAN / BBAN or they have 2 numbers. Can somebody help me with this?

Liej · ‎12-19-2012

The worksheet showen in the pdf is created in Excel, and that's how the SAS-output should be. (fische excel.xls) I don't need to have colors in my output, it was only to indicate the different type of fields. Green fields are data taken from SAS, blue field are calculated and the white fields are tekst. The excel-file is based on a SAS-dataset like this table. The data is sorted based on Number (asc) and Start_date (desc). Number Start_date Date_Fin Code_1 Code2 12345 01-06-2012 30-06-2012 76 4 12345 01-05-2012 31-05-2012 85 5 12345 01-04-2012 30-04-2012 80 4 12345 01-03-2012 31-03-2012 83 5 12345 01-02-2012 29-02-2012 79 3 12345 01-01-2012 31-01-2012 81 4 45678 01-05-2012 30-06-2012 80 6 45678 01-03-2012 30-04-2012 79 5 45678 01-01-2012 29-02-2012 83 7 78912 01-06-2012 30-06-2012 80 5 Based on this table I should create 3 excel-files IFI_12345, IFI_45678 and IFI_78912. Can I do this with Proc Report or should I keep using the call execute? temp.xml is an example created with the call execute methode. As you can see there is no layout.

Liej · ‎12-18-2012

I made a little pdf-print from an excel-file example. The green fields I need to take from a SAS-dataset and the blue fields are calculated. Is there an easier way to make such an excel-file with a lay-out like that based on a SAS-dataset?

Liej · ‎12-14-2012

Hey guys, I was wondering if there's a solution for my problem. I need to create a lot of excel-files, more then 300, based on SAS-data. There's an extra difficulty, the data in the excel-files need to be displayed in a specific way. This is the code I use to create my excel-files. (attachment) The only problem I have is that the excel-files are created on the SAS-server. Can I change my code so the excel-files are created on my PC or a shared folder? David

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

Re: Create indexed table

Re: Create indexed table

Create indexed table

Re: Create Exel file from dataset

Re: Create Exel file from dataset

Re: Create Exel file from dataset

Create Exel file from dataset

Re: Split dataset in multiple datasets

Re: Split dataset in multiple datasets

Re: Split dataset in multiple datasets

Re: Create indexed table

Re: Create indexed table

Create indexed table

Re: Create Exel file from dataset

Re: Create Exel file from dataset

Re: Create Exel file from dataset

Create Exel file from dataset

Re: Split dataset in multiple datasets

Re: Split dataset in multiple datasets

Re: Split dataset in multiple datasets

Split dataset in multiple datasets

Re: Creating excel-files with SAS-EG

Re: Creating excel-files with SAS-EG

Creating excel-files with SAS-EG