If you just use the SASPY function to convert the dataset in question into a dataframe without any additional options being used what does it do with the date variable that is formatted to display dates DMY order?  Does it make it into something that Python understands is a DATE (or possibly a DATETIME value with the time of day part set to midnight)?  Does it convert your numeric variable with the user defined format to a number? Or does it instead pull over the display values as a character string?   Can you be more specific about what steps are involved in your in house process?  Are they things that you do in Python after getting the data from the dataset? Or are they things that are changing how the SASPY function is called.  It kind of looks like you are saying it is somehow making a list of variables that it should tell the SASPY function to convert to string into stead of numbers.  How is it deciding which variables to ask for that conversion?  Do you have some master metadata that is driving the process?  Is it looking at the SAS datasets and making decisions?  If the later then perhaps it does not know that DDMMYY is a SAS supplied format that is used for displaying dates and is instead treating it the same as your other numeric variable that does have a user defined format and so is treating the date variable like your other coded variables.   ... View more

Let's be the macro processor and see what you asked it to do. You called the macro with ID=ID and MOD=MOD. So the first %IF will become: %if MOD = N %then ... Which is never true. The comparison fails on the first letter since the letter M does not equal the letter N. So we need to evaluate the %ELSE branch instead.  That has another %IF that will look like: %if %EVAL(MOD in 6 8 9) %then ... And again this can never be true no matter what value you set for the MINDELIMITER option since the string MOD does not appear after the IN operator.  PS There is no need for %EVAL here since %IF always used %EVAL() to evaluate the test expression.  You might leave the () to make it easier for the humans to read.   I suspect your logic actually depends on the value of the dataset variable MOD so you need to code IF statements and not %IF statements. ... View more

INDEXW() will work.  You can use CATS() function strip leading/trailing spaces from HIGHCUSTID (or convert it to a string if it is numeric). ON indexw(b.CAT,cats(a.HighCustID),',') If you want to ignore the case of any letters in the ids the you might want to use FINDW() instead as it has a modifiers arguments you can add to ignore case.  It also has modifiers to remove the spaces. ON findw(b.CAT,a.HighCustID,',','it') ... View more

Assuming you want to PUT the names (not retrieve them from some unspecified place) just use a PUT statement with quoted strings.  You probably only want to write the names once say on the first observation. data _null_; set sashelp.class end=last ; if _n_=1 then put @15 'Name' @(30-2) 'Sex' @40 'Age' @50 'Height' @60 'Weight' ; /* Check if the observation is the 1st, 15th, 6th, or the last observation */ if _n_ in (1, 15, 6) or last then do; /* Use PUT with column positioning correctly */ put @15 name $10. @30 sex $1. @40 age 3. @50 height 5.1 @60 weight 5.1 @70 ; end; run; Result Name Sex Age Height Weight Alfred M 14 69.0 112.5 James M 12 57.3 83.0 Philip M 16 72.0 150.0 William M 15 66.5 112.0 ... View more

So that first photograph is of some screen in Enterprise Guide that is trying to show you what is in your SAS dataset?  Can you run PROC CONTENTS on the dataset instead and show what it outputs for that CODTOP variable?   Looks to me CODTOP is a numeric variable with a user defined FORMAT attached to it that display the numbers in the data as the strings you showed in your first photograph.   Also it seems you have a multiple step process that is transferring the data from SAS.  Are you using some type of ETL process or tool? Is it a publicly available tool? Which one? Or something developed in house? ... View more

"Column Headings"?   Are you just looking for how to attach LABELS to your variables? proc print data=product_data label noobs; label prodnum='Product Number' prodname='Product Name'; run; ... View more

If you want to name the variables using strings that are not valid SAS names you have to first set the VALIDVARNAME option to ANY. options validvarname=any; After that you will have to use NAME LITERALS in your code to create or reference any name that is not a normal valid SAS name.    Your example SQL code does not have any steps that would make it possible to rename the variables, since all you are showing are UPDATE statements.    But in other more normal SQL code you could rename the variables when you list them in the SELECT clause of your CREATE TABLE statement. create table want as select prodnum as 'Product Number'n , prodname as 'Product Name'n , manunum as 'Manufacturer Number'n ... Or use the RENAME= dataset option on either the output dataset reference create table want(rename= (prodnum='Product Number'n prodname='Product Name'n manunum='Manufacturer Number'n ....)) as select .... Or rename them on the way IN (remember to use the new names in the code) ... from have(rename=(....)) ... Not sure why you are using SQL code for this problem however.  It would much simpler in normal SAS code. data want; set product_data; if (prodtype = 'Software') then rtlcost = rtlcost * 1.20;   else rtlcost = rtlcost * 0.80; run; But if you are going to use the UPDATE statement of PROC SQL your example can be done in one STEP by using a CASE clause to select the right cost adjustment factor for each observation. update product_data set rtlcost = rtlcost * case when (prodtype = 'Software') then 1.20 else 0.80 end; ... View more

SAS does not have FORMAT with that name. 69 %put current_datetime = %sysfunc(datetime(), datetimeampm.); ERROR: Format name DATETIMEAMPM. not found or the width and/or decimal specified for the format used are out of range. current_datetime = 2048627120.622 Did you mean to use the DATEAMPM format instead?  Or perhaps DATEAMPM23.? 70 %put current_datetime = %sysfunc(datetime(), dateampm.); current_datetime = 30NOV24:11:08:28 PM 71 %put current_datetime = %sysfunc(datetime(), dateampm23.); current_datetime = 30NOV2024:11:09:40 PM   ... View more

Are your ID values valid SAS names? Transpose your non formula values to a single observation.  First see if PROC TRANSPOSE can handle it. proc transpose data=have(where=(missing(formula))) out=wide(drop=_name_ ) ; id id; var score; run; If it cannot you can generate CODE to make the WIDE dataset instead. filename code temp; data _null_; file code; set have; where not missing(score); put id '=' score ';' ; run; data wide; %include code ; run; Now use the other observations to generate code to set the SCORE (and ID and FORMUAL variables). And include the generated code to calculate the score and write the observations. filename code temp; data _null_; file code; set have; where not missing(formula); put id=:$quote. ';' formula= :$quote. ';score=' formula ';output;' ; run; Now read in the WIDE dataset to get the SCORE values for the IDs in question.  Also read in the HAVE dataset (but not any of the data) to set the type/length/format of the variables. data want; set have(obs=0) wide; %inc code ; keep id score formula; run; You could generate bot code files in a single pass through the HAVE dataset.  So two passes total.  See below.  Remove the WHERE statement if you want the result to have all of the original observations.   filename code1 temp; filename code2 temp; data _null_; set have; if missing(formula) then do; file code1; put id '=' score ';' ; end; else do; file code2 ; put 'when (' id :$quote. ') score=' formula ';' ; end; run; data wide ; %include code1 ; run; data want; if _n_=1 then set wide ; set have; where not missing(formula); select (id); %inc code2 ; otherwise ; end; keep id score formula ; run;     ... View more

What did the log say for each of your two steps?  Did you get any errors or warnings or unexpected notes?   Why did you create a dataset named work.AI_Derm and then run PROC CONTENTS on AI_Derm? Normally those mean the same dataset, but they could mean two different datasets if you have USER library assigned.   Are you sure you are looking at the same XLSX file as SAS is reading from? ... View more

Interesting problem.  And a good chance to learn some new things about enhanced XLMV2 engine options that make it easier to deal with XML files that are inside of ZIP files (such as XLSX files).   So basically what I see is that the XLSX file has an XML file named xl/workbook.xml that contains a SHEETS tab with the worksheet names.  And potentially it also has a RANGES tag that has the list of named ranges (if any exist).  It might also have files named xl/worksheets/_rels/sheet<#>.xml.rels where the # is the suffix on the ID tag for the SHEET.  In these xml.rels files are RELATIONSHIP tags which have a field named TARGET that might point to files with names like ../tables/table<#>.xml.  Those table#.xml files will have NAME and RANGE tags.   At least with SAS version 9.4 (TS1M7) you can use the XMLV2 libref engine to read a file that you pointed a fileref using the ZIP filename engine. filename _wb zip "myfile.xlsx" member="xl/workbook.xml"; libname _wb xmlv2 xmlfileref=_wb xmlmap=_map ; If you include tables in your XMLMAP definition that don't appear in the XML file you are reading there is no error generated.  You just get an empty table.  So no need for multiple MAP files, one file can cover all three XML file types.   If you use FILENAME() and LIBNAME() functions and HASH objects you can find all of the worksheets, named ranges and "ranges formats as tables" from and XLSX file in one data step.   /* Get list of Sheets, Named Ranges, and Format as Table Ranges from an XLSX file Needs the XML MAP file to read the XML file. Needs two inputs: XSLX filename and target dataset name Output is dataset with: TYPE Type of range: RANGE = Named Range SHEET = Sheet name TABLE = Name of "format as Table" areas ORDER Order within TYPE NAME Name of sheet/name/table REFERENCE String to use for SHEET= or RANGE= statements of proc import to read the range. XLSXFILE Path of the source XLXS file This program will use the following fileref and libref names xlsxmap - File with XML mapping for the XLM files used from the XLSX file _wb - Point to workbook.xml file _rels - Point to .xml.rels files _tbl - Point to table#.xlm files */ %let xlsxfile=c:\downloads\tables.xlsx; %let xlsxfile=c:\downloads\PRAMS-MCH-Indicators-2016-2021.xlsx; %let xlsxfile=c:\downloads\tables_ranges.xlsx; %let dsn=xlsx_ranges; * Make XML map file ; filename xlsxmap temp; data _null_; file xlsxmap; put '<SXLEMAP version="2.1">' / '<!-- Table definitions for xl/workbook.xml -->' / '<TABLE name="sheets">' / ' <TABLE-PATH>/workbook/sheets/sheet</TABLE-PATH>' / ' <COLUMN name="Id">' / ' <TYPE>character</TYPE>' / ' <LENGTH>32</LENGTH>' / ' <PATH>/workbook/sheets/sheet/@r:id</PATH>' / ' <DATATYPE>string</DATATYPE>' / ' </COLUMN>' / ' <COLUMN name="Name">' / ' <TYPE>character</TYPE>' / ' <LENGTH>32</LENGTH>' / ' <PATH>/workbook/sheets/sheet/@name</PATH>' / ' <DATATYPE>string</DATATYPE>' / ' </COLUMN>' / '</TABLE>' / '<TABLE name="ranges">' / ' <TABLE-PATH>/workbook/definedNames/definedName</TABLE-PATH>' / ' <COLUMN name="Name">' / ' <PATH>/workbook/definedNames/definedName/@name</PATH>' / ' <TYPE>character</TYPE>' / ' <DATATYPE>string</DATATYPE>' / ' <LENGTH>200</LENGTH>' / ' </COLUMN>' / ' <COLUMN name="Range">' / ' <PATH>/workbook/definedNames/definedName</PATH>' / ' <TYPE>character</TYPE>' / ' <DATATYPE>string</DATATYPE>' / ' <LENGTH>200</LENGTH>' / ' </COLUMN>' / '</TABLE>' / '<!-- Table definitions for xl/worksheet/table#.xml -->' / '<TABLE name="table_range">' / ' <TABLE-PATH>/table</TABLE-PATH>' / ' <COLUMN name="Id">' / ' <PATH>/table/@id</PATH>' / ' <TYPE>character</TYPE>' / ' <DATATYPE>string</DATATYPE>' / ' <LENGTH>32</LENGTH>' / ' </COLUMN>' / ' <COLUMN name="Name">' / ' <PATH>/table/@name</PATH>' / ' <TYPE>character</TYPE>' / ' <DATATYPE>string</DATATYPE>' / ' <LENGTH>100</LENGTH>' / ' </COLUMN>' / ' <COLUMN name="Range">' / ' <PATH>/table/@ref</PATH>' / ' <TYPE>character</TYPE>' / ' <DATATYPE>string</DATATYPE>' / ' <LENGTH>200</LENGTH>' / ' </COLUMN>' / '</TABLE>' / '<!-- Table definition for .rels files -->' / '<TABLE name="relationship">' / ' <TABLE-PATH>/Relationships/Relationship</TABLE-PATH>' / ' <COLUMN name="Id">' / ' <PATH>/Relationships/Relationship/@Id</PATH>' / ' <TYPE>character</TYPE>' / ' <DATATYPE>string</DATATYPE>' / ' <LENGTH>32</LENGTH>' / ' </COLUMN>' / ' <COLUMN name="Target">' / ' <PATH>/Relationships/Relationship/@Target</PATH>' / ' <TYPE>character</TYPE>' / ' <DATATYPE>string</DATATYPE>' / ' <LENGTH>200</LENGTH>' / ' </COLUMN>' / '</TABLE>' / '</SXLEMAP>' ; run; data _null_; length type $5 order 8 name $32 reference $200; length xlsxfile dsn memname fname msg $200 fileref libref $8 rc 8; length id $32 target range $200; call missing(of _all_); xlsxfile = symget('xlsxfile'); dsn = symget('dsn'); * Point libref at workbook.xml file ; memname="xl/workbook.xml"; fileref='_wb'; libref='_wb'; link make_libref; if rc then stop; * Setup hash object and iters ; * RANGES - This is the output results ; declare hash ranges(ordered:'YES'); ranges.definekey('type','order'); ranges.definedata('type','order','name','reference','xlsxfile'); ranges.definedone(); * SHEETS - Read from xl/workbook.xml file ; declare hash sheets(dataset:'_wb.sheets',ordered:'YES'); sheets.definekey('Id'); sheets.definedata('Id','Name'); sheets.definedone(); declare hiter sheet('sheets'); * NAMED_RANGES - Read from xl/workbook.xml file ; declare hash named_ranges(dataset:'_wb.ranges',ordered:'YES'); named_ranges.definekey('Name'); named_ranges.definedata('Name','Range'); named_ranges.definedone(); declare hiter named_range('named_ranges'); * RELS - Read from xl/worksheets/sheet#.xml.rels file ; declare hash rels; declare hiter rel; * TABLES - Read from xl/tables/table#.xml files ; declare hash tables; declare hiter table; * Add each named range ; eof0=named_range.first(); do ord=1 by 1 while(0=eof0); type='RANGE'; order=ord; reference=name; ranges.add(); eof0=named_range.next(); end; * Add each sheet ; eof1=sheet.first(); do ord=1 by 1 while(0=eof1); sheetid=id; sheetname=name; type='SHEET'; order=ord; reference=name; ranges.add(); * Point libref at this sheets relationships file (if it exists) ; libref='_rels'; fileref='_rels'; memname=cats('xl/worksheets/_rels/sheet',substr(id,4),'.xml.rels'); link make_libref; if not rc then do; * Read in the sheet relationships ; rels = _new_ hash(dataset:'_rels.relationship(where=(target like ''../tables/table%.xml''))'); rels.definekey('Id'); rels.definedata('Id','Target'); rels.definedone(); rel = _new_ hiter('rels'); eof2= rel.first(); do while(0=eof2); * Point libref at this tables definition file ; fileref='_tbl'; libref='_tbl'; memname=cats('xl/tables/',scan(target,-1,'/')); link make_libref; if rc then put 'ERROR: Unable to assign libref for table range.' target= memname= fileref= libref=; else do; * Read in the table range file ; tables = _new_ hash(dataset:'_tbl.table_range'); tables.definekey('Id'); tables.definedata('Id','Name','Range'); tables.definedone(); table = _new_ hiter('tables'); range=' '; eof3= table.first(); type='TABLE'; order=input(substr(scan(target,-2,'./'),6),32.); reference=catx('$',sheetname,range); ranges.add(); tables.delete(); end; rc=libname('_tbl'); rc=filename('_tbl'); eof2=rel.next(); end; rels.delete(); end; rc=libname('_rels'); rc=filename('_rels'); eof1=sheet.next(); end; * Close libref/fileref ; rc=libname('_wb'); rc=filename('_wb'); * Write ranges hash to dataset ; ranges.output(dataset: dsn); return; make_libref: * Make a libref pointing to XML file inside XLSX file. ; * Input vars: xlsxfile, memname, fileref, libref ; * Output vars: rc ; * Point fileref to member in XLSX file ; rc=filename(fileref,xlsxfile,'zip',cats('member=',quote(trim(memname)))); if rc then do; msg=sysmsg(); put 'ERROR: Unable to create ' fileref= 'pointing to ' memname= 'in ' xlsxfile= ; put 'ERROR- ' msg ; end; else rc=fileref(fileref); if not rc then do; * When member file exists then point libref to it ; rc=libname(libref,,'xmlv2',catx(' ','xmlfileref=',fileref,'xmlmap=xlsxmap access=readonly')); if rc then do; msg=sysmsg(); put 'ERROR: Unable to create XMLV2 libref pointing to ' memname=; put 'ERROR- ' msg ; end; end; return; run; proc print; run; Example Results: NOTE: Processing XMLMap version 2.1. NOTE: There were 3 observations read from the data set _WB.sheets. NOTE: There were 1 observations read from the data set _WB.ranges. NOTE: Processing XMLMap version 2.1. NOTE: There were 2 observations read from the data set _RELS.relationship. WHERE target like '../tables/table%.xml'; NOTE: Processing XMLMap version 2.1. NOTE: There were 1 observations read from the data set _TBL.table_range. NOTE: Processing XMLMap version 2.1. NOTE: There were 1 observations read from the data set _TBL.table_range. NOTE: Processing XMLMap version 2.1. NOTE: There were 1 observations read from the data set _RELS.relationship. WHERE target like '../tables/table%.xml'; NOTE: Processing XMLMap version 2.1. NOTE: There were 1 observations read from the data set _TBL.table_range. NOTE: The data set WORK.XLSX_RANGES has 7 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.04 seconds Use the REFERENCE value with the SHEET= statement for TYPE=SHEET and with the RANGE= statement for the other two types. proc import dbms=xlsx file="c:\downloads\tables_ranges.xlsx" out=Name1 replace; range="Name1"; run; proc import dbms=xlsx file="c:\downloads\tables_ranges.xlsx" out=WasSheet2 replace; sheet="WasSheet2"; run; proc import dbms=xlsx file="c:\downloads\tables_ranges.xlsx" out=WasTable2 replace; range="Sheet1$C6:E7"; run; Here is another file that had a different type of relationship record for one of the sheets which why the WHERE= dataset option is used to only find the relationships to tables. NOTE: Processing XMLMap version 2.1. NOTE: There were 6 observations read from the data set _WB.sheets. NOTE: There were 1 observations read from the data set _WB.ranges. NOTE: Processing XMLMap version 2.1. NOTE: There were 0 observations read from the data set _RELS.relationship. WHERE target like '../tables/table%.xml'; NOTE: The data set WORK.XLSX_RANGES has 7 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.03 seconds   ... View more