About fre

fre · ‎06-12-2019

Thank you Ksharp & Patrick for giving me more insight into this matter.

fre · ‎06-12-2019

Hi, I would like to add a date to the name of a dataset. This date (reference_date) has been defined somewhere in the beginning of my code. In the example in the following code, I would like to have a tablename like "RESULTS_2005_010519" The following code did not work, what could be wrong with it? %macro backup_results; %let reference_date= '1MAY2019'd; /* other code */ data test.RESULTS_2005_%sysfunc(datepart(%sysfunc(input(&reference_date.,DDMMYY10.)))); set test.RESULTS_2005; run; %mend;

fre · ‎11-13-2018

Hi FreelanceReinhard, thank you a million times for your code. It's far above my skilsl, but I think I can adapt it for my use. In the example, year and month are already sorted (ascending) in the first place. If I would start from an unsorted dataset, should I first sort it (proc sort), or does this code works without sorting? EDIT: I've managed to apply your code to my original dataset. Such a beautiful and efficient code. I've learned a lot today. I'm very thankful to this Community such free support exists, and of course in particular to you, Reinhard!

fre · ‎11-13-2018

Dear Community I’m looking for a program for the following. What I have is a table HAVE. It has 3 fields: CD_YEAR, CD_MONTH, var_A. This table contains 50 records: The first 20 records are all CD_YEAR = 2018 and CD_MONTH = 1 The next 30 records are all CD_YEAR = 2018 and CD_MONTH = 2 What I need is a table WANT, where for var_A, a value is filled in following a statistical distribution. I’m simplifying here: let’s say the distribution is: 50% => var_A = 10 25% => var_A = 15 25% => var_A = 20 So in table WANT, the needed result is: First 10 records (cd_year= 2018, cd_month = 1) => var_A = 10 Next 5 records (cd_year= 2018, cd_month = 1) => var_A = 15 Next 5 records (cd_year= 2018, cd_month = 1) => var_A = 20 Next 15 records (cd_year= 2018, cd_month = 2) => var_A = 10 Next 7 or 8 records (cd_year= 2018, cd_month = 2) => var_A = 15 Next 7 or 8 records (cd_year= 2018, cd_month = 2) => var_A = 20 Notice the last 2 lines here above: as 25% of 30 records is not a whole number (7,5), it should take 7 or 8 records for var_A = 15. By consequence, it should take resp. 8 or 7 records for var_A = 20. I hope I explained it clearly enough so that anyone could understand what I want. I can’t see a method how to implement this. Any hints or directions how to deal with this would be very appreciated. (the real distribution is a little more complicated, with 13 percentages - I used the above example just to illustrate) Thanks

fre · ‎10-15-2018

Thank you Kurt, the %str( ) did the job.

fre · ‎10-15-2018

Hi, thank you for your replies I've tested both of your answers, but the space keeps existing between the variable names. This is what I used to call the macro: %rename_vars(my_dataset, ,_); I've also tried to put the space between ' ' and " ", but that didn't help either. Could it be that something is still missing in the code?

fre · ‎10-15-2018

Dear Community, I'm refering to this topic: https://communities.sas.com/t5/SAS-Data-Management/Replacing-quot-quot-with-a-quot-quot-for-variable-names-in-a/td-p/174192 This topic is closed (locked), so I have to start a new thread for my question: The solution presented in this topic only removes the spaces in the variable names, it doesn't replace them with an underscore. What should be adapted to the code in the solution so that a variablename "variable 1" will be replaced by "variable_1"? This is the code: %macro rename_vars(table, remove_char); %let table=%upcase(&table); %let rename_list=; proc sql noprint; select cats("'",name,"'n =",compress(name, "&remove_char")) into :rename_list separated by " " from sashelp.vcolumn where upper(libname)="%scan(WORK.&table,-2)" and upper(memname)="%scan(&table,-1)" and findc(strip(name), "&remove_char")>0; quit; %put rename_list: %bquote(&rename_list); %if %bquote(&rename_list) ne %then %do; proc datasets lib=%scan(WORK.&table,-2); modify %scan(&table,-1); rename &rename_list; run; quit; %end; %mend; /* call the macro. First parameter is the table name, second parameter the characters to be removed in variable names */ %rename_vars(sample,_ -);

fre · ‎08-21-2018

Thank you for your help. It's clear now.

fre · ‎08-21-2018

Dear community In one of my programs, I do a conversion from numeric to text. This is my code: data test ; format CD_YEAR $4.; format CD_KD $3.; set test0; CD_YEAR = put(jr,$4.); CD_KD = put(kd,$3.); run; What I don't understand is that my log gives the following warnings: WARNING: Variable jr has already been defined as numeric. WARNING: Variable kd has already been defined as numeric. I know jr and kd are numeric, that's why I want to convert them to text... What is happening here and how can I prevent such warnings?

fre · ‎10-05-2017

Hi FredrikE, gamotte, RW9, thank you all very much for your answers. There are more possibilities, but I can only mark 1 as the solution, so I picked the one closest to my original code.

fre · ‎10-05-2017

Dear community, I have this dataset to start from: case / name / valueX 001 / A / 100 002 / A / 80 002 / B / 20 003 / A / 20 003 / B / 50 003 / C / 30 What I need is for each case, the maximum for valueX, but the output need also to have the column "name". So, I want: 001 / A / 100 002 / B / 80 003 / B / 50 What I tried: proc sql; create table want as select case, name, max(valueX) as max_value from have group by case; quit; but this results in: 001 / A / 100 002 / A / 80 002 / B / 80 003 / A / 50 003 / B / 50 003 / C / 50 What am I doing wrong?

fre · ‎07-11-2017

Thank you for clearing that out. I was using the import data in Excel (that didn't work), but indeed it works fine when you just use 'open file'. Excel does a pretty good job, it's a pitty that SAS cannot do this. For my purposes, I have 300 such XML files, all with different schemes. So manually open each file in excel + convert to csv is not an option. I'll have to look further to find something that can import all those files automatically. Maybe VBA or something like that.

fre · ‎07-11-2017

Hi RW9, Thank you for your reply. Seems like Excel cannot handle this xml. + I guess XML mapper is limited when it comes to more complex XML. I'm wondering if SAS can handle such format. I don't know any other alternatives at the moment. If anyone knows any alternative, feel free to reply...

fre · ‎07-11-2017

Dear Community I need to import XML-structured data. This is how the code looks like: <gebeurtenisDatablok xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="geb:GebeurtenisDatablok" xmlns:geb="urn:be:vlaanderen:omgeving:datablok:gebeurtenis:GEB_WIJZIGEN_CONTACT_001:001"> <persoon xmlns:dossier="urn:be:vlaanderen:omgeving:dossier:v2" xsi:type="dossier:RechtsPersoon"> <persoonId databron="KBO" externIdWaarde="0000000000" /> <hoedanigheid> <naam>AANVRAGER</naam> <contact> <postadres gebruik="POA"> <subadres></subadres> <straat> <naam>no street</naam> </straat> <huisnummer>100</huisnummer> <busnummer></busnummer> <postcode>1000</postcode> <gemeente> <naam>Brussels</naam> </gemeente> <regio>Vlaams Gewest</regio> <land>Country</land> </postadres> <medium aard="TEL">000000000</medium> <medium aard="EMAIL">first.last@server.com</medium> <medium aard="FAX"></medium> <medium aard="GSM"></medium> </contact> </hoedanigheid> <maatschappelijkeNaam>name ZZZ</maatschappelijkeNaam> <commercieleNaam>name XXX</commercieleNaam> <rechtsvorm>VZW</rechtsvorm> </persoon> </gebeurtenisDatablok> So far, I've tried 2 things: - import via libname xxx xmlv2 - using XML Mapper In both cases, SAS / XML mapper reports that it can not handle the XML. Does anyone know how to import this data into a regular SAS dataset or what else I could try? Any help or advice is highly appreciated. Kind regards

fre · ‎06-27-2017

Thank you Tom for your suggestion. I've integrated the XML code in the original question. Hope this is more clear now.

Online Status	Offline
Date Last Visited	‎06-28-2023 08:48 AM

Re: PROC Report & ODS excel: define columnwidth in function of #byval

Re: PROC Report & ODS excel: define columnwidth in function of #byval

Re: PROC Report & ODS excel: define columnwidth in function of #byval

PROC Report & ODS excel: define columnwidth in function of #byval

Re: How to left align _INFILE_ in an email

How to left align _INFILE_ in an email

Re: User Input Interface (user forms to input data)

Re: User Input Interface (user forms to input data)

Re: importing data stops SAS in the middle of a textfile without gener...

Re: importing data stops SAS in the middle of a textfile without gener...

Re: PROC Report & ODS excel: define columnwidth in function of #byval

Re: PROC Report & ODS excel: define columnwidth in function of #byval

Re: User Input Interface (user forms to input data)

Re: Merge all datasets in library (not work)

Re: Merge all datasets in library (not work)

Re: how to assign values based on condition

Re: Max by group + keep variables

Re: add date to dataset name

add date to dataset name

Re: how to assign values based on condition

how to assign values based on condition

Re: Replacing " " with a "_" for variable names in a given dataset

Re: Replacing " " with a "_" for variable names in a given dataset

Replacing " " with a "_" for variable names in a given dataset

Re: Warning in log - why?

Warning in log - why?

Re: Max by group + keep variables

Max by group + keep variables

Re: Import complex XML

Re: Import complex XML

Import complex XML

Re: data import & XML