Can you please assist.
Below is a sample from an xml we are trying to read. We don’t have the mapper for it and any other for that matter.
Currently we run a library pointing to the http file as below. We then run a proc dataset which gives us the datasets that are in the library. Each <item#> tags are basically rows which SAS interprets as a dataset. So if there are 1000 rows (<item0> to <item999>), it means there would be 1000 datasets to read, and hence means hitting the database a 1000 times.
Could you please help us with a better way of reading this, hopefully a way that will allow us to read all rows once.
Libname wsdl xml92 "http://ncss/Webservice/capacity/method/xml/day/&DATE";
<?xml version="1.0"?>
<response><item0><Day>2016-10-06</Day><Period>0</Period><LoadForecast>0</LoadForecast><RevisedForecast>0</RevisedForecast><DayAheadAvail/><HydroAvail>0</HydroAvail><AdditionalHydro>0</AdditionalHydro><OCGTAvail>0</OCGTAvail><MaxAvail>0</MaxAvail><MinCapacity>0</MinCapacity><ActualSentout>22008</ActualSentout><RequiredInst>700</RequiredInst><RequiredReg>600</RequiredReg><RequiredTmr>700</RequiredTmr><RequiredInstActual>0</RequiredInstActual><RequiredRegActual>0</RequiredRegActual><RequiredTmrActual>0</RequiredTmrActual><DayAheadAdditionalSpinning>0</DayAheadAdditionalSpinning><TotalCurrentLoadLosses>-3503</TotalCurrentLoadLosses><TotalLoadLosses>-3503</TotalLoadLosses><TotalUnplanned>-665</TotalUnplanned><TotalAdditional>1600</TotalAdditional><TotalDeviation>-2568</TotalDeviation><TotalUnitDeviations>-665</TotalUnitDeviations><OperatingRsv>-2568</OperatingRsv><Surplus>-2568</Surplus><Colour>Red</Colour></item0><item1><Day>2016-10-06</Day><Period>1</Period><LoadForecast>0</LoadForecast><RevisedForecast>0</RevisedForecast><DayAheadAvail/><HydroAvail>0</HydroAvail><AdditionalHydro>0</AdditionalHydro><OCGTAvail>0</OCGTAvail><MaxAvail>0</MaxAvail><MinCapacity>0</MinCapacity><ActualSentout>21684</ActualSentout><RequiredInst>700</RequiredInst><RequiredReg>600</RequiredReg><RequiredTmr>700</RequiredTmr><RequiredInstActual>0</RequiredInstActual><RequiredRegActual>0</RequiredRegActual><RequiredTmrActual>0</RequiredTmrActual><DayAheadAdditionalSpinning>0</DayAheadAdditionalSpinning><TotalCurrentLoadLosses>-3518</TotalCurrentLoadLosses><TotalLoadLosses>-3518</TotalLoadLosses><TotalUnplanned>-665</TotalUnplanned><TotalAdditional>1600</TotalAdditional><TotalDeviation>-2583</TotalDeviation><TotalUnitDeviations>-665</TotalUnitDeviations><OperatingRsv>-2583</OperatingRsv><Surplus>-2583</Surplus><Colour>Red</Colour></item1> </response>
Regards,
Thokozani
Once you got these tables, you can combine them all together into a table. filename x temp; libname xx xmlv2 '/folders/myfolders/temp.xml' xmlmap=x automap=replace; data want; set xx.item: ; run;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.