SAS Support Communities

Yes: decode - you can replace this in a couple of ways, formats, or case when <> then <> else <> end statements. to_number - you can replace this with input(<>,best.) to_char - you can replace with put(<>,best sign - again number of different ways but case when <> < 0 then -1 when <> =0 then 0 else 1 end (note <> is your logic or variable).   Also note, I dont think that SQL code will wok as you expect as you are aggregating (sum,count) by a group, but you also have where clauses and variables which are not aggregated.   ... View more

Hi: TAGSETS.EXCELXP does NOT, NOT, NOT, ever create an XLSX file. TAGSETS.EXCELXP can ONLY create a simple .XML file which contains Spreadsheet Markup Language 2003 XML tags for a workbook/worksheet. The "official" file extension is .XML. If you use a .XLS file extension, typically, Excel will open the file, but display a message about the file extension not matching the contents. With TAGSETS.EXCELXP you should NEVER use XLSX as the file extension. With ODS EXCEL, on the other hand, the ONLY file extension you should use is .XLSX. If you truly used ODS EXCEL, then you should be able to open the .XLSX file with Excel, if you a creating the file on a Unix box, you might have to move the .XLSX file to a Windows machine so you can open the file. If you are using TAGSETS.EXCELXP, try using the simple .XML file extension and then moving the file to a system with Excel to open the file. cynthia ... View more

I am not sure that your response to this question understood what I was asking: 2) describe the exact output desired by the macro. Since your apparent desire is to have random values inserted into the data then there are LOTS of things that would have to be considered, such as what values are assingned to any other variables involved in the data set - Ans2: I have different formats for each variable passing through the macro. Based on the raw values for each field corresponding formats will be applied If you have variables a, b, c in the data and you are doing what ever you want to call this process to the data to generate your new dataset with the formatted values of A, what would be the values for variables B and C? If they are not in the result then I submit that proc tabulate may be a valid choice with the OUT= option to create a data set. One of the things in the output is a _type_ variable to tell you which table to select to generate your single variable datasets. But if you need any thing other than the single variable's formatted value and a count you need to be very explicit about where the values for any other variables come from.   ... View more

@Kurt_Bremser Thanks a lot for the solution on this 🙂 ... View more

@Kurt_Bremser Thanks a lot for the solution on this 🙂 ... View more