cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Sudhan
‎06-12-2020
  • 8 Posts
  • 1 Likes Given
  • 1 Solutions
  • 0 Likes Received

Re: Combining Datasets on variable field length

by Fluorite | Level 6 in SAS Programming
‎06-08-2020 08:48 AM
‎06-08-2020 08:48 AM
I did something like this and it helped.   proc sql; create table Out_file as select * from Table_A outer union corr select * from Table_B ; quit; ... View more

Re: Combining Datasets on variable field length

by Fluorite | Level 6 in SAS Programming
‎06-08-2020 07:18 AM
‎06-08-2020 07:18 AM
Thank you. 1 The SAS System 11:07 Monday, June 8, 2020 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program (2)'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH=''; 6 %LET _CLIENTPROJECTPATHHOST=''; 7 %LET _CLIENTPROJECTNAME=''; 8 %LET _SASPROGRAMFILE=''; 9 %LET _SASPROGRAMFILEHOST=''; 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=ACTIVEX; 13 GOPTIONS XPIXELS=0 YPIXELS=0; 14 FILENAME EGSR TEMP; 15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 16 STYLE=HtmlBlue 17 STYLESHEET=(URL="############################################################################### 17 ! 3F/Root/VFS/ProgramFilesX86/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 18 NOGTITLE 19 NOGFOOTNOTE 20 GPATH=&sasworklocation 21 ENCODING=UTF8 22 options(rolap="on") 23 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 24 25 GOPTIONS ACCESSIBLE; 26 %macro union(dsn1=, /*Name of the first data set */ 27 dsn2=, /*Name of the second data set */ 28 out= /*Name of combined data set */); 29 options mprint; 30 proc contents data=&dsn1 noprint 31 out=out1(keep=name type length where=(type=2)); 32 33 proc contents data=&dsn2 noprint 34 out=out2(keep=name type length where=(type=2)); 35 run; 36 37 data _null_; 38 file "combined.sas"; 39 merge out1 out2(rename=(length=length2)) end=last; 40 by CDNUMPOL; 41 if _n_ = 1 then put "Data &out;"; 42 l = max(length,length2); 43 put " length " CDNUMPOL " $ "12";"; _____ 49 NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 44 if last then do; 45 put " set &dsn1 &dsn2;"; 46 put "run;"; 47 end; 48 run; 49 50 %include "combined.sas"; 51 2 The SAS System 11:07 Monday, June 8, 2020 52 %mend union; 53 54 %union(dsn1=MIG_TEST.TRANSFORMED_DATA,dsn2=TRANSFORMED_ARCHV,out=Out_file); MPRINT(UNION): options mprint; MPRINT(UNION): proc contents data=MIG_TEST.TRANSFORMED_DATA noprint out=out1(keep=name type length where=(type=2)); NOTE: The data set WORK.OUT1 has 334 observations and 3 variables. NOTE: Compressing data set WORK.OUT1 increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(UNION): proc contents data=TRANSFORMED_ARCHV noprint out=out2(keep=name type length where=(type=2)); MPRINT(UNION): run; NOTE: The data set WORK.OUT2 has 335 observations and 3 variables. NOTE: Compressing data set WORK.OUT2 increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.00 seconds cpu time 0.01 seconds MPRINT(UNION): data _null_; MPRINT(UNION): file "combined.sas"; MPRINT(UNION): merge out1 out2(rename=(length=length2)) end=last; MPRINT(UNION): by CDNUMPOL; NOTE: Line generated by the invoked macro "UNION". 54 put "Data &out;"; l = max(length,length2); 54 ! put " length " CDNUMPOL " $ "12";"; if last then do; put " set &dsn1 &dsn2;"; put "run;"; end; run; _______ 49 22 54 ! %include MPRINT(UNION): if _n_ = 1 then put "Data Out_file;"; MPRINT(UNION): l = max(length,length2); MPRINT(UNION): put " length " CDNUMPOL " $ "12";"; MPRINT(UNION): if last then do; MPRINT(UNION): put " set MIG_TEST.TRANSFORMED_DATA TRANSFORMED_ARCHV;"; MPRINT(UNION): put "run;"; MPRINT(UNION): end; MPRINT(UNION): run; NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. ERROR 22-322: Syntax error, expecting one of the following: a name, #, +, @. NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds WARNING: Physical file does not exist, /XXX/YYYY/sas/bin/SASConfigFolder/Lev1/SASXXXX/combined.sas. ERROR: Cannot open %INCLUDE file combined.sas. 55 56 GOPTIONS NOACCESSIBLE; 3 The SAS System 11:07 Monday, June 8, 2020 57 %LET _CLIENTTASKLABEL=; 58 %LET _CLIENTPROCESSFLOWNAME=; 59 %LET _CLIENTPROJECTPATH=; 60 %LET _CLIENTPROJECTPATHHOST=; 61 %LET _CLIENTPROJECTNAME=; 62 %LET _SASPROGRAMFILE=; 63 %LET _SASPROGRAMFILEHOST=; 64 65 ;*';*";*/;quit;run; 66 ODS _ALL_ CLOSE; 67 68 69 QUIT; RUN; 70 ... View more

Combining Datasets on variable field length

by Fluorite | Level 6 in SAS Programming
‎06-08-2020 06:13 AM
‎06-08-2020 06:13 AM
Hi,   I am quite new to SAS. I am trying to combine to datasets of variable field Lengths.   Example:  Table_A   IDENTIFIER| DC_001|DC_002| ------------------------------------------------ 100| Sample_Text |(blank)|   Table_B IDENTIFIER| DC_001|DC_002| ------------------------------------------------ 500|Sam|Sample|   Output should be stored in a table like Output_Table IDENTIFIER| DC_001|DC_002| ------------------------------------------------ 100| Sample_Text |(blank)| 500|Sam|Sample|   I am trying to use the following macro, but i have a error and not sure where I have gone wrong   %macro union(dsn1=, /*Name of the first data set */ dsn2=, /*Name of the second data set */ out= /*Name of combined data set */); proc contents data=&dsn1 noprint out=out1(keep=name type length where=(type=2)); proc contents data=&dsn2 noprint out=out2(keep=name type length where=(type=2)); run; data _null_; file "combined.sas"; merge out1 out2(rename=(length=length2)) end=last; by IDENTIFIER; if _n_ = 1 then put "Data &out;"; l = max(length,length2); put " length " IDENTIFIER " $ "12";"; if last then do; put " set &dsn1 &dsn2;"; put "run;"; end; run; %include "combined.sas"; %mend union; %union(dsn1=TABLE_A,dsn2=TABLE_B,out=OUTPUT_Table); ERROR 22-322: Syntax error, expecting one of the following: a name, #, +, @. ... View more

Re: Append data to a table and increment a flag on the resultant table

by Fluorite | Level 6 in SAS Programming
‎06-02-2020 07:05 AM
‎06-02-2020 07:05 AM
Thanks for your response. But i need the DT_FLAG>=2, as this data will be used for a report. ... View more

Append data to a table and increment a flag on the resultant table

by Fluorite | Level 6 in SAS Programming
‎06-02-2020 06:43 AM
‎06-02-2020 06:43 AM
I have two tables on SAS "Table_A" and "Table_A_Archive", as part my ETL process "Table_A" is created on a daily basis and the data should be archived on "Table_A_Archive". When the data is archived on "Table_A_Archive" a a flag will be created/updated "DT_FLAG". On the first day this is how the table would look like "Table_A" | ID | Load_Date ------ ------------- | 100 | 01JUN2020:12:13:56 "Table_A_Archive" | ID | Load_Date | DT_FLAG ------ --------------------- --------- | 100 | 01JUN2020:12:13:56 | 1 On Day 2 "Table_A" | ID | Load_Date ------ ------------ | 101 | 02JUN2020:12:13:56 "Table_A_Archive" | ID | Load_Date | DT_FLAG ------ --------------------- --------- | 100 | 01JUN2020:12:13:56 | 2 | 101 | 02JUN2020:12:13:56 | 1 The new data should be loaded with the DT_FLAG of 1 and old records DT_FLAG should be incremented by 1. Load_Date is the pivotal point. I have written a SAS code but it seems a bit messy, can someone please help me with a SAS Datastep   %macro Cntl_archive(table_name=,arch_table_name=); %GLOBAL WRK; %if %sysfunc(exist(&arch_table_name.)) %then %do; proc append base=&arch_table_name. data=&table_name. force; run; proc sql; Create table TEMP as Select distinct Load_Date,Load_Date as WRK from &arch_table_name. order by Load_Date desc ;quit; proc rank data=TEMP descending out=TEMP; var WRK; ranks count; run; data &arch_table_name. (drop=DT_FLAG); set &arch_table_name.; run; proc sql; Create table &arch_table_name. as Select T0.*,T1.count as DT_FLAG from &arch_table_name. T0 inner join TEMP T1 on T0.Load_Date=T1.Load_Date ;quit %end; %else %do; data &arch_table_name.; set &table_name.; DT_FLAG= 1; IS_ACTIVE=''; run; %end; %mend Cntl_archive; ... View more

Update a column in a table with values from another table

by Fluorite | Level 6 in SAS Programming
‎03-05-2019 02:26 AM
‎03-05-2019 02:26 AM
Hi,   As part of my programming, I have a query which updates a TARGET table with values from the SOURCE table.   proc sql; update SCHEMA.TARGET as u set &COLUMN_NAME. =(select value from &COLUMN_NAME. as n where strip(u.ID)=strip(n.ID)) ;quit; COLUMN_NAME is an attribute on TARGET table, it is a table as well. I am trying to update the column with values from the corresponding table. The variable values will be changed dynamically.   Can you please advise how to achieve this using data steps, this approach seems to be a bit slow.   Thank you.     ... View more

Modify Column data types and format on a table based on another table

by Fluorite | Level 6 in SAS Programming
‎03-04-2019 04:55 AM
‎03-04-2019 04:55 AM
Hi,   I am quite new to SAS, pardon me if my question is naive.   I want to change my target table definition (TARGET) based on my Source table (SOURCE) column data type    As part of my programming first I create a table TARGET with the default data type and column length as char(30).   "AS-IS" Table definition for TARGET: | Name  | Type | Length | ID                 | Char | 30 | Name           | Char | 30   "AS-IS" Table definition for SOURCE: | Name  | Type | Length | ID                  | Char      | 30 | Name            | Numeric | 8   TARGET table is a flat/empty table, where I need to update the values from the Source table. So first I need to convert the data type of the TARGET table based on the SOURCE table.   "To-Be" Table definition for TARGET: | Name  | Type | Length | ID                  | Char      | 30 | Name            | Numeric | 8   Following is the code I have written   proc sql; Create table Test( name char(14)) ; quit; proc sql; select cat(type,'(',length,')'),format into :Dtype,:format from dictionary.columns where libname = 'WORK' and memname = 'SOURCE' and upcase(name)='VALUE' ; quit; proc sql; alter table WORK.Test modify name &Dtype. format=&format.; quit; I have an error message:  ERROR: You cannot alter 'name' to be a numeric column. ERROR: Character column name requires a character format specification. ... View more

Data step to create tables based on a column value

by Fluorite | Level 6 in SAS Enterprise Guide
‎02-05-2019 12:50 AM
‎02-05-2019 12:50 AM
Hi,   I would like to create a table dynamically based on the column available in a different table.   Example: My source_table would look like Field_No | field_Name 1            |   A 3            |  C 2            |  B   My target_table should look like  A | B | C   Currently my solution is not so efficient.   /*Dynamic table creation*/ %let s1=; /*Column lenght should be 30 characters so I am creating a dummy variable*/ %let Dummy= 'Dummy_Dummy_Dummy_Dummy_Dummy_Dummy_Dummy'; proc sql; create table TEMP as select 'Hi' as Work from Temp_table where 1=2 ; quit; proc sort data = Source_table by Field_No; run; proc sql; select Dummy||" as "||fld into :s1 seperated by "," from (select "&Dummy" as Dummy,Compress(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(Compress(Compress(Compress(substr(strip(upcase(field_name)),1,30)),"("),")"),'*',''),' ',''),'.',''),'-',''),'>',''),,'s') as FLD from Source_table) ; quit; proc sql; create table target_table as select "&Dummy." as value_1,&s1 from TEMP where 1=2; quit; Appreciate your support. ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎06-12-2020 07:10 AM
Likes given to
View All
Quick links