Vince28_Statcan Tracker

Re: How to import this SDMX-ML data from Statistics Canada in SAS?

Vince28_Statcan — Fri, 22 Aug 2014 17:20:06 GMT

Hi Morglum,

I am glad to see non governmental users tap into our dissemination data. I am not posting on behalf of Statscan but myself only. Fereeza kindly pointed out the issue via e-mail. Our dissemination formats for complete files are indeed not the simplest to load directly into SAS. The beyond20/20 option is a good alternative for users that may not have the corporate IT infrastructure to use external XML tools and expertise to handle SDMX.

However, from personal experience on a project a few months ago where I was waiting for a database access and ran out of patience and extracted a SDMX which contained the specific information I needed at that point in time, I can say that it is feasible to import the SDMX files directly into SAS. Our dissemination website provides links to SDMX documentation but the basic gist is that the STRUCTURE file is metadata about the main file namely in terms of codesets definitions.

The SAS xml engine uses sequential access and thus shouldn't have any issue handling large files. As a matter of fact, I modified slightly my .map file from a few months back and extracted the ~35M records from the data file into SAS via only the XML engine before coming here to comment. SDMX however does not comply to the GENERIC xml in the xml libname engine documentation hence why you specifically need to build a custom XMLMAP for the job and the libname statement in your original post did not work.

A good place to get started in a scenario like this is to read the XML file as a text file via INFILE statement, put that into a large enough variable and limit yourself with OBS= infile option (I use between 100-1000). Then in another datastep output that into a different file to check the xml structure without crashing windows from out of memory exception because most default xml viewers would load the entire 15gb file into memory. Use a simple text viewer to see the structure and build your custom XMLMAP with it. There are different ways to map the file and then transform it into what you wish to obtain.

If you have tried the XML map option at some point but that it failed and that failure wasn't caused by a faulty .map file, it might be because of the XML file encoding or because you did not expand logical record length (LRECL= option) for the file and the XML structure broke down from truncation.

Proc groovy is a layer to call java from within SAS. There probably exists a free java class online that reads SDMX - I wouldn't know where to begin to look. The data step component object Java Object could've been used in a similar way but again that requires some external java expertise.

I am glad to see you managed to import the data. I just came by to share that it is possible to import into SAS via custom xmlmap with some effort.

Vincent

Re: Using the XML Mapper Utility

Vince28_Statcan — Tue, 06 May 2014 13:00:16 GMT

I can't seem to open the XML links but given the construct in your post, you should be able to read that with the mapper in 2 different ways. The simplest being to make sure that your

<TABLE-PATH>

has tue full hierarchy all the way up to and including /contract

</TABLE-PATH>

This acts as your row-creating driver. That is, doing so you will have one row created in your dataset each time you run through this hierarchy.

The second thing to do is to define the column SYMBOL using the options retain and replace like

...

</COLUMN>

These 2 things combined should allow you to easily create the desired table from the specified xml. If you can specify the full x-path to /contract (since I can't seem to view the xml link), I can probably provide you with a .map file that will meet your requirements.

Re: Analysis by row

Vince28_Statcan — Fri, 11 Apr 2014 13:48:32 GMT

Well this may be a little simplistic but as I don't know the full extent of your data structure, here's how you can work it out:

step 1. Proc Sort by Name, Loan_Pos /* name or whatever other unique client ID you have */

step 2. Data step using BY group on Name (or whatever other unique client ID you have */

...

if first.name then output table1;

else output table2;

run;

If you can have significantly more than 2 loans per individuals and your problem is more complex than that, you would need to throw in an example of a "worst case scenario" where you want to distribute X records over Y files according to some logic like you've described.

Vince

Re: SAS converting character variables to numeric while exporting to CSV

Vince28_Statcan — Wed, 26 Mar 2014 17:26:09 GMT

Alternatively, you could also use RW9's approach with throwing double quotes around but then further more adding an = sign in front of each text string such that instead of having

name,valuec,valuen

"Vincent","12345678901234567890",12345678901234567890

you have

name,valuec,valuen

="Vincent",="12345678901234567890",=12345678901234567890

Reading the equal sign in a cell will tell excel it is a formula. In the case of a string formula aka "12345678901234567890", it will then only display it as a string.

Vincent

Re: SAS converting character variables to numeric while exporting to CSV

Vince28_Statcan — Wed, 26 Mar 2014 17:23:32 GMT

Excel is doing the formating/calculations (yes sadly it calculates and potentially loses precision).

Upon opening the CSV in excel,

1. go to the main menu

2. excel options

3. advanced

4. under display options for this worksheet click show formulas in cells instead of their calculated values

There might be some details online to have this set as the default when you open excel but I don't think you could help it on other people's desktop using CSV. CSV contains no metadata and its up to whatever software you use to open it to chose how it does so. Sadly, MS tries so much to accomodate people in having to do less manipulation that they best guess format and calculation wrong, even with DSD (quotes around strings).

Re: If then statement to case statement

Vince28_Statcan — Mon, 24 Mar 2014 16:55:57 GMT

There are some inconsistencies with your output table versus the logic. You might want to review both. Examples of it would be row1 flag1 num_var1 is not in (4,5) which makes the condition if table1.num_varn not in (4,5) or table2.charvarn in (table2.char_var) as true and thus the flag should be 0, not 1. My first guess was that you should've had an AND instead of an OR between the two conditions. However, this was also rejected by row2/flag4 because this would evaluate to true even if the OR had been replaced by an AND.

Anyway, I can still provide conceptual grounds for you to work with. The concept of "... in table2.char_var" can be achieved through the use of subquerries as pointed out above. The SQL syntax looks like

table1.charvarn in (select char_var from table2)

This turns the subquerry in parenthesis as a list of values for the in operator. That is, it's as though the subquerry in the parenthesis resolves to ('a', 'b', 'c', 'd') from the example data above.

Vincent

Re: using %sysfunc(cat() )

Vince28_Statcan — Fri, 28 Feb 2014 20:50:15 GMT

Explanation of your error as the proper fix was covered by Quentin

As with data step DO loops, the %DO also ends by iterating one beyond the end of loop criterion. So at the end of the %do loop &K is effectively 5. You can create a dataset with a do loop and not drop the counter variable and see for yourself.

So the issue with your code is that the macro first writes the following code during macro compilation phase and then executes this datastep code:

data wilcox1;

wil_stat1=resolve('&&wilstat&k');

...

p_val4=resolve('&&pval&k');

output;

run;

That is, the resolve function resolves macro variable at data step execution phase rather than macro compilation phase as it was within your %sysfunc.

So timeliness wise, since the data step is executed after the macro code has been resolved, your code effectively turns into this conceptual hardcoded datastep with only macro variables:

data wilcox1;

wil_stat1=&wilstat5;

p_val1=&pval5;

wil_stat1=&wilstat5;

p_val1=&pval5;

wil_stat1=&wilstat5;

p_val1=&pval5;

wil_stat1=&wilstat5;

p_val1=&pval5;

output;

run;

So the error lies in using the resolve function and the corrections were covered by Quentin and Reeza.

Re: proc contents

Vince28_Statcan — Fri, 28 Feb 2014 20:35:50 GMT

proc contents is a hidden call to proc datasets with content statements. If you follow through the documentation of proc contents for DATA= option, you will find that

proc contents data=jim._ALL_;

run;

should achieve your desired result. The notice for the MEMTYPE= in the documentation only applies if you want proc contents for library members that are not datasets as those are the default member types output.

If you are looking only for a particular element of information from proc contents though, the SASHELP tables mentionned by Reeza are probably a better alternative if you have many, many datasets in your library.

Re: Sas merge help

Vince28_Statcan — Fri, 24 Jan 2014 18:26:47 GMT

You simply need to use a join statement for your desired result. My original reply should provide exactly this without the specific ordering.

You can add an order by statement with either the appropriate variable names like

order by t2.id2, t1.id, t1.code

or reuse the syntax by ctorres of using column numbers

order by 1,2,3 will use the columns produced by the merge so 1 is equivalent to t2.id2 because that is the first one in the select statement. t1.* means all variable from source t1 so they would be in the exact same order as they were in the source meaning 2 is equivalent to t1.ID and 3 is equivalent to t1.code

All in all, my original code will achieve the merge you ought to do. Sortation can either be embeded in the proc sql through order by statement or you could've simply ran a proc sort on the table produced by my code.

Re: Sas merge help

Vince28_Statcan — Fri, 24 Jan 2014 17:50:14 GMT

Hi Cypher,

Technically, this is a many-to-many merge over the key ID.

Sadly, this is not achiveable with a data step MERGE statement. The natural alternative is to use proc sql;. There are 2 options for data step, one is to manually control the merge with multiple set statements and whatnot. This is extremely tedious and error prone. The other one is to use the hash object with multidata: 'YES' option. However, this is memory dependant and while it may offer improved execution time, it is more transferable to use proc sql and also far better to learn proc sql before hash objects in SAS.

The sql code would look like this:

proc sql;

create table want as

select t2.ID2, t1.*

from dataset1 as t1

inner join

dataset2 as t2

on t1.id = t2.id;

quit;

inner join is subjective as your data didn't have any nonmatch to figure out whether left/right/inner/full joins would've been the best pick

Vince

Re: how to do left join in sas dataset

Vince28_Statcan — Fri, 17 Jan 2014 14:43:23 GMT

You would first need to check your syntax and error log if a piece of code does not work. If it doesn't produce expected results, then it might be a functionality does not oblige to your requirements.

proc sql;

create table want as

select label, value

from dataSet1 left join dataSet2

on start<=value<=end

group by label

order by start;

run;

quit;

There is also typically a good practice when using SQL to use the dot notation to prefix variable names as per their source i.e.

proc sql;

create table want as

select a.label, a.value

from dataSet1 as a left join dataSet2 as b

on b.start<=a.value<=b.end

group by a.label

order by b.start;

quit;

Additionally, I don't think (but I might be wrong here) that SQL standards use the group by statement in a similar fashion to SAS data step with a merge statement. That is, to achieve the equivalent by statement, you would normally want to have something like

proc sql;

create table want as

select a.label, a.value

from dataSet1 as a left join dataSet2 as b

on a.label=b.label and

b.start<=a.value<=b.end

group by a.label

order by b.start;

quit;

Furthermore there is an ambiguity with your order by statement (At least based on my best guess of what you were trying to achieve) since the LEFT join retains records from dataset1 which did not match and thus have no start and end values tied to the record.

The SQL group by statement is used to define what summary functions "sumarize by". The general idea is that all variables in the group by statement are found in the select statement and all other variables in the select statement use sumary functions so as to generate a single record per group by unique key. Otherwise, you generate a cartesian product.

Beyond that, I must agree with LinusH. Data step is not appropriate for all merges and it is why proc sql exists and is built in sas. It might be viable depending on your data but likely not as it is even less fuzz match friendly than proc sql (the fuzz occur with start<=value<=end condition). Other data step alternatives involving hash object are more flexible in terms of what can be achieved (when compared with a merge statement) but it is best to be familiar with proc sql prior to getting into the hash object as the object has memory limitations.

Hope this helps

Vincent

Re: ERROR: The %DO statement is not valid in open code.

Vince28_Statcan — Thu, 16 Jan 2014 20:06:24 GMT

The following code works just fine when testing

%MACRO DO_BRANCH;

%do u=1 %to 20;

proc surveyselect data=sashelp.class method = urs sampsize = 14 OUTHITS

reps=1 seed=1234100 out=new_&u;

run;

%end;

%MEND DO_BRANCH;

%DO_BRANCH;

However, 2 important things to note. Since all procedure resets to the start of the seed, your current implementation would yield new_1=new_2=...=new_20. That is, the exact same 141 records would be selected.

I suspect its the part in CODES... that triggers your empty new_2 to new_20

As DN pointed out, a more natural approach would be to use REPS=20 and then split the resulting file BY REPLICATE (the automatically generated variable listing the count of REPS). This would clear your issue with having to change seeds each time as then it keeps on drawing numbers from the same seed rather than reseting at the beginning of the seed for each procedure iteration.

Re: Amortization Schedule Using Array?

Vince28_Statcan — Tue, 07 Jan 2014 19:24:31 GMT

data test;

array loan_bal{0:72};

loan_bal{0}=amf; /* initiate debt */

do i=1 to 72;

loan_bal{i}=loan_bal{i-1}-(pmt-(loan_bal{i-1}*(rate/12)));

end;

run;

by explicitely definining the array index starting with 0, you handle start

however, this implementation does not handle the last partial payment nor the possibility that loan_bal{i} goes below 0 if term < 72 months but the "lag to prev value" is there and working. The where statement is in a distinct category from the IF statement as far as implementing logic goes in SAS.

Vincent

Re: Excel commands via DDE

Vince28_Statcan — Tue, 07 Jan 2014 15:08:16 GMT

https://communities.sas.com/thread/49210

See my last post or follow the following link

http://support.microsoft.com/kb/128185/en-us

Vincent

Re: Select observations that meet a criteria

Vince28_Statcan — Mon, 06 Jan 2014 17:32:01 GMT

As PG has examplified by-group processing, I will only answer the hash portion.

The SAS Hash Object is a fairly recent feature. It is essentially a "lookup table" that is usable through the data step. The strength of Hashing for the specific question you've asked is that if your data wasn't already sorted according to your by-group, then a hash solution alleviates the need to pre-sort (which often is the lenghtiest part of a simple by-group process). However, the hash object is fully held in memory throughout the data step so if you have a very, very large volume of data, it is possible to face an "out of memory" error with hash solutions.

Syntaxically, it also looks confusing at first glance but one gets familiar with the programing syntax quite fast. Since you mentioned you were fairly new to SAS, it is probably best to get accustomed at least to by-group processing before looking at hashing. Hashing can achieve far more than just this but since it is memory-limited, it is wiser to learn the processing tools that should work "all around". So I will refrain from providing a hash solution. There are plenty of similar examples around these forums anyway and in various SGF papers.

As to add one element to Keith's description above, procedures have various built-in output. Unless tasked otherwise, the output statement in proc summary outputs outputs many more statistics than just maximum. Using output statement options allows you to control that default behaviour. See proc summary -> output statement documentation (it will actually point you towards proc means' output statement as they have the same options). You will find the specific explanation for the MAXID option there and how it applies the max on X, by class HH_NO but will actually output PI_No and X as the identifier for that maximum point found.

Vincent

Re: sort columns from lft to right after proc transpose

Vince28_Statcan — Fri, 03 Jan 2014 16:32:49 GMT

Transpose creates variables in the order they are encountered in the variable(s) used to create new ones after the transpose. There are different approaches to tackle the problem depending on how large a file and how long it takes to process.

You could consider adding a "dummy" record to your untransposed file at the very beginning of the file recreating the desired dates in descending order instead of the ascending order it naturally gets updated. You could also proc sort descending or order by desc on your date variable before transposing.

Ultimately, you could use the transposed dataset and recreate a new dataset with the variables in a different order after the shot. This can be done with some macros, the dictionary tables and a keep statement or really anything that will make the data step encounter the variables name in the order you want them to be before the set statement. I will only examplify this very case simply as its the only one that requires some more complex programming statements.

proc sql;

select name

into :dt1-:dt9999 /* generic upper boundary on the number of date variables - only the necessary macro vars will be created */

from sashelp.vcolumn

where libname="WORK" /* or whatever library your NOW dataset is stored in */

and memname = "NOW"

and prxmatch("m/^[A-Za-z]{3}[0-9]{4}/", name)>0 /* validates that the variable name is following 3 char 4 digit patterns. If you have other odd variable names you could be more specific and build a list of 3 letter prefixes corresponding to months or do further year processing in the regular expression. I assumed it was not required */

;

quit;

%let ndates=&sqlobs; /* to be able to loop on dt1-dt&sqlobs. after other subsequent proc sql */

proc sql;

select name

into :leads separated by ' '

from sashelp.vcolumn

where libname="WORK" /* or whatever library your NOW dataset is stored in */

and memname = "NOW"

and prxmatch("m/^[A-Za-z]{3}[0-9]{4}/", name)=0 /* other non-match variables will be put in front of all date vars, you can change this rule if only a few specific variables are to be leads that is merely my assumption of your intentions */

;

quit;

%macro write();

data NEXT_MONTH;

keep &leads.

%do i=&ndates. %to 1 %by -1;

&&dt&i..

%end;

; /* to close the keep statement */

set NOW;

run;

%mend;

%write();

This is all untested so there might be some syntax errors but the idea is all there. Please note that SASHELP.VCOLUMN is naturally sorted by column number hence the reverse %do loop. There are alternative approaches like grouping and ordering inside the proc sql "descending" and then doing a regular %do loop

Re: Select observations that meet a criteria

Vince28_Statcan — Fri, 03 Jan 2014 14:13:02 GMT

The issue although it may also be the desired result as it was not specified, is that if you have 2 distinct PI_NO with the same x=max(x) value, you will output both with that proc sql approach.

Similarly, depending on the size of your data, using proc summary instead of a data step or sql solution could take far more time than it should.

Again, depending on the size of your data and whether it is already pre-sorted by HH_NO or not, there are hash solutions, by group processing with point= solution and others that could get you faster processing and solve the proc sql potential duplicate HH_NO issue.

So anyway if the solutions above run too slow or don't fully comply with your requirement, I will gladly provide either a hash or by processing solution but just in case you did not have such a huge dataset and either the above worked, I'll wait for a reply to provide an alternative solution.

Re: Problem using Input to convert char with missing values to num

Vince28_Statcan — Fri, 03 Jan 2014 14:06:01 GMT

building upon appropriate input is typically better than the first solution as multiplying char by numeric issues a warning in the log and those should be avoided for any large process.

could you provide a datalines example with data that does not get input properly by solution #2? Odds are you can apply some simple string function to WERT inside the input function to make it inputable appropriately. Likely strip to remove blanks or possibly the same solution you've mentionned using an alternative input format after removal of periods.

Re: Proc template can handle next case?

Vince28_Statcan — Fri, 03 Jan 2014 13:33:55 GMT

Well, since you are stuck with SAS9.1.3, I'm affraid you will have to stick to the data step approach. You should be able to use macros and dictionary tables to at least relieve some of the coding burden of your table with 100 columns.

I'm not knowledge enough of proc template to tell you with certainty that it can be achieved or not but realistically though, I don't see how it could be faster than a data step with a single read on the data. I've spent a bit of time looking at proc template documentation yesterday as I had been meaning to learn the basics of it for a long time but could never find a project that required it.It turns out that even if you were to define a tagset specific to your task in proc template and then use proc print to do the output, proc print still has a lot of additionnal processing going on that does not occur in a data step. There are tons of Events that drive the output layout and since proc template is really meant to be a visual output/reporting/graphing tool much more than a flat file output, any custom template reuses the ODS-driven events that are bound to the procedures with output. Anyway in short, even if you did spend the time to build a custom tagset, it should still be slower than the data step solution you've posted originally as it will necessarily involve a lot of additionnal dynamic processing of ODS-driven procedure events.

Here's the line in "What's new in SAS9.2 XML libname engine" discussing the new XMLMap feature for exporting purpose (it worked for imports with the XML libname engine but export was only implemented for engine XML92)

New XMLMap
Functionality

XMLMap functionality has the following enhancements for the XML92 engine nickname:

You can now export an XML document from a SAS data set using the XMLMap that
was created to import the XML document. The XMLMap tells the XML engine how to
map the SAS format (variables and observations) into the specific XML document
structure. See Exporting
XML Documents Using an XMLMap.

Re: Proc template can handle next case?

Vince28_Statcan — Thu, 02 Jan 2014 14:31:36 GMT

Alright I got to give it a go earlier than expected

Linked files are those relevant to your objectives. This was done in SAS9.2 and I don't think the xml engine was fully functional before then (at least not the xml92 engine). It further got improved with SAS9.3 if you need to create multiple files but I don't have it installed so you'd need to look at documentation as otherwise, you would need to create 1 libname statement per output file desired (even if they used the same map) whereas I believe from reading about it a while back the SAS9.3 improvements help alleviate that at least for input, maybe not for output though.

please notice the "thisnamemustmatch" both in the sas code and in the map file.

the <TABLE name="thisnamemustmatch"> tag in the map HAS to be the same as the data set output name that you used with your xml engine as otherwise it causes a client crash. You can name it however you want - it does not appear in the output xml anyway.

As to provide some brief indications to adapt it to your desired solutions on how to write the map:

TABLE-PATH is essentially what dictates how often records are created for extraction and thus conversely impacts how the xml output appears. For example, test the same code with the map only altering

<TABLE-PATH syntax="XPath">/Table/RECORD</TABLE-PATH>

<TABLE-PATH syntax="XPath">/Table</TABLE-PATH>

and see the difference in the output xml file.

As well, if you have hierarchical data that has columns "carried over" - think a household survey where you have one tag with all household level data and then many subtags with person-level data - If you were to create a person-level table that keeps all the household level data for each record, you'd have to add retain="YES" attribute to all of the COLUMN tags which use household-level data in the map. However, for export which is what your original request was, this is somewhat moot as per the requirement of 2D tables. You would've needed to merge a household table with a person table into a single table with repeated household data for each person before exporting.

Also, I don't think it is possible to use _N_ directly as a column to export. However, unless the existing sortation is absolutely essential to save, it is fairly typical not even to bother with an instance= attribute or the like as it is not required to import the data back into SAS. In particular, you could even create a new rowid at extraction time even if you did not create an instance= attribute using <INCREMENT-PATH> subtags of the <COLUMN> tag in the map. That is, the xml engine is able to mimic a _N_ column at extraction time and since it reads and writes the data sequentially, the numbers would be the same.

Hope this helps and that my comments aren't all too confusing

Vincent