SAS Macro: How to Retrieve a Value from a Dataset

December 24, 2009 by
Filed under: Uncategorized 

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

SAS is a very powerful language when one have to deal with processing huge datasets. It becomes much more complicated when one wants to play with single elements. I recently had the need to put a specific value of a dataset into a macro variable. With the help of the SAS support team of Switzerland, I have made a small macro for that. Here is the definition of the function:

  • Macro name: Get_data
  • Input: myDataset (name of the dataset), myLine (line number), myColumn (name of the column), myMVar (name of the macro variable used to store the result)
  • Output: A new macro variable named &myMVar.

And here is the corresponding code:

%MACRO Get_data(myDataset=,myLine=,myColumn=,myMVar=);
%GLOBAL &myMVar.;
data _null_;
set &myDataset.;
if _N_ = &myLine. then do;
call symput(symget('myMVar'),&myColumn.);
end;
run;
%MEND Get_data;

Of course, I would be very interested if someone found a simplier way of performing this action (and I think SAS support would be interested as well).

Share

Comments

42 Comments on SAS Macro: How to Retrieve a Value from a Dataset

  1. lordthias on Thu, 24th Dec 2009 2:54 pm
  2. Hi Sandro,
    Here’s an other way to do the same think (maybe a little faster on big dataset)

    %MACRO Get_data(myDataset=,myLine=,myColumn=,myMVar=);
    %GLOBAL &myMVar;
    proc sql noprint;
    select &myColumn into :&myMVar
    from &myDataset
    where monotonic() = &myLine;
    quit;
    %MEND Get_data;

    %Get_data(myDataset=tablename,myLine=linenumber,myColumn=colname,myMVar=varname)
    %put &varname;

    I let you test the execution time ;)

    Joyeuse fête de noël !

  3. Sandro Saitta on Thu, 24th Dec 2009 4:05 pm
  4. @lordthias: Thanks for the PROC SQL example. I’m not using the get_data macro on big data sets for the moment, but I would of course compare both codes before using it on huge files :-) A toi aussi et bonne année!

  5. Chris Hemedinger on Thu, 24th Dec 2009 4:16 pm
  6. I second the suggestion for using PROC SQL SELECT INTO. The concept of sequential records holds true for SAS datasets, but if your data is in a DBMS, you might want a different mechanism for pulling out a specific record (value in an ID column, for example).

  7. Sandro Saitta on Sat, 26th Dec 2009 7:35 pm
  8. @Chris: Thanks for sharing your opinion. Also PROC SQL is closer to mySQL and may be worth considering if planning to move from SAS to another ETL tool (Talend, etc.)

  9. Shane on Wed, 30th Dec 2009 9:59 am
  10. Another benefit of PROC SQL is it doesn’t require sorted datasets for a join… where as DATA …; MERGE … BY … will throw an error if it is not sorted — very annoying!!

  11. Sandro Saitta on Sun, 3rd Jan 2010 1:23 pm
  12. @Shane: Good point! This is something we should keep in mind when comparing DATA step with PROC SQL execution time.

  13. Manish on Thu, 14th Jan 2010 12:01 pm
  14. Hi

    This is the simplest and the most efficient code I can think of (would be hapy to learn better way to do this).

    —————————————————————-

    %MACRO Get_data(myDataset=,myLine=,myColumn=);
    %GLOBAL myMVar;
    data _null_;
    pull = &myLine; /*assigning value of macto var to datastep*/
    set &myDataset (keep=&myColumn) point=Pull;
    call symput(‘myMVar’,&myColumn);
    output; stop;
    run;
    %MEND Get_data;

    %Get_data(myDataset=,myLine=,myColumn=);
    %put &myMVar; /*to print value of macro var in log*/

    —————————————————————-
    It is effiencie because it will read only the line that you want SAS to read. If ‘If – Then’ is used, sas reads each obs and then does the evalauation of IF condition, more input-output. This can be optimised by telling sas to read only the required row using ‘Point=’ option. But ‘Point=’ option takes only variable name. Hence, before set statement, a variable called ‘Pull’ is defined with the value of required row-number.. Now sas just reads the required row saving i/o. DO NOTE that ‘Output;’ and ‘Stop’ must be used with ‘Point=’ option of you will execute a never-ending data-step;

    I hae further optimised the program by using ‘KEEP=’ optin in set statement, which asks SAS to read only relevant columns.

    The syntax of ‘Call Sumput’ is actually simpler than what you have used here. You don’t need to use Symget here.

    Hope this helps.

    Manish

  15. Sandro Saitta on Thu, 14th Jan 2010 12:47 pm
  16. @Manish: Thanks for your example Manish. I haven’t tested it yet, but it seems (really) optimized by only reading the correct row/column. Thanks for your input!

  17. Song Liu on Thu, 18th Feb 2010 9:03 pm
  18. Use sas I/O funtions would be effient. Here is the code:
    %MACRO Get_data(myDataset,myLine,myColumn);
    %global myMvar;
    %let dsid=%sysfunc(open(&myDataset.,i));
    %let rc=%sysfunc(fetchobs(&dsid, &myLine.));
    %if &rc = 0 %then %let myMvar=%sysfunc(GETVARN(&dsid,%sysfunc(varnum(&dsid,&myColumn)))); /****use getvarc for character variables*****/
    %let rc=%sysfunc(close(&dsid));
    %MEND Get_data;

  19. RolandRB on Tue, 1st Feb 2011 3:55 am
  20. I have a macro called %getvalue to do this that uses SAS I/O functions as above. The advantage with this method is that you can have a function-style maco to do this that allows you to call it in the middle of a data step if need be.

    http://www.datasavantconsulting.com/roland/Spectre/utilmacros/getvalue.sas

    More macros here:
    http://www.datasavantconsulting.com/roland/Spectre/maclist2.html

  21. Sandro Saitta on Thu, 3rd Feb 2011 8:23 pm
  22. @RolandRB: Thanks for the links!

  23. fab on Sun, 8th Apr 2012 11:27 am
  24. I look for something like ‘setvalue’
    is it possible to write a value of a macro variable to a certain observation in a data set?
    I want to navigate with the line number as well.
    data and point with symget?

  25. Android Training in Chennai on Mon, 9th Feb 2015 12:56 pm
  26. Hi this is Kathiresan i am having 3 years of experience as a dot net developer and i am certified. i have knowledge on OOPS concepts in .NET but dont know indepth. After learning android will be enough to get a good career in IT with good package? and i crossed Android Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

    Thanks,
    kathiresan

  27. Android Training in Chennai on Mon, 9th Feb 2015 1:33 pm
  28. Hi this is Kathiresan i am having 3 years of experience as a dot net developer and i am certified. i have knowledge on OOPS concepts in .NET but dont know indepth. After learning android will be enough to get a good career in IT with good package? and i crossed Android Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

    Thanks,
    kathiresan

  29. Oracle Training on Wed, 18th Feb 2015 8:30 am
  30. Your blog is really useful for me. Thanks for sharing this informative blog. If anyone wants to get real time Oracle Training in Chennai reach FITA located at Chennai. They give professional and job oriented training for all students.

  31. SAP Training on Sat, 28th Feb 2015 12:26 pm
  32. Thanks for sharing this informative blog. FITA provides SAP Training in Chennai with years of experienced professionals and fully hands-on classes. SAP is one of the CRM. Today’s most of the IT industry use this software for customer relationship management. To know more details about sap reach FITA Academy. Rated as No.1 SAP Institutes in Chennai.

  33. Oracle Training in Chennai on Mon, 2nd Mar 2015 1:55 pm
  34. Your blog is really useful for me. Thanks for sharing this informative blog. If anyone wants to get real time Oracle Training in Chennai reach FITA Oracle Training Institutes in Chennai. They give professional and job oriented training for all students.

  35. Salesforce Training on Fri, 6th Mar 2015 10:52 am
  36. Thanks for sharing this informative blog. FITA provides Salesforce Training in Chennai with years of experienced professionals and fully hands-on classes. Salesforce is a cloud based CRM software. Today’s most of the IT industry use this software for customer relationship management. To know more details about salesforce reach FITA Academy. Rated as No.1 Salesforce Training Institutes in Chennai.

  37. Digital Marketing Training on Wed, 11th Mar 2015 7:41 am
  38. Thanks for sharing this informative blog. Recently I did Digital Marketing Training in Chennai at a leading digital marketing company. It’s really useful for me to make a bright career. To know more details about this course please visit FITA.

  39. Android Training Institutes in Chennai on Sat, 14th Mar 2015 7:20 am
  40. Your blog is really useful for me. Thanks for sharing this useful blog..Suppose if anyone interested to learn Android Training in Chennai please visit fita academy which offers best Android Course in Chennai at reasonable cost.

  41. Android Training in Chennai on Wed, 18th Mar 2015 7:53 am
  42. Thanks for your informative article. Android SDK allows you to create stunning mobile application loaded with more features and enhanced priority. With basis on Java coding language, you can create stunning mobile application with ease. Best Android Training institute in Chennai

  43. Android Course in in Chennai on Wed, 18th Mar 2015 7:55 am
  44. Thanks for your informative article. Android SDK allows you to create stunning mobile application loaded with more features and enhanced priority. With basis on Java coding language, you can create stunning mobile application with ease.

  45. Ios Training in Chennai on Thu, 19th Mar 2015 9:09 am
  46. Excellent post on iOS mobile apps development!!! The future of mobile application development is on positive note. You can make most it by having in-depth knowledge on mobile application development platform and other stunning features.

  47. SEO Training on Wed, 25th Mar 2015 6:51 am
  48. Your information is really useful for me.Thanks for sharing such a valuable information. If anyone wants to get SEO Training in Chennai visit FITA Academy located at Chennai. Rated as No.1 SEO Training institute in Chennai.

  49. Jenny on Tue, 7th Apr 2015 8:41 am
  50. dataset is really helpful. and can retrive a value from the dataset.Loadrunner Training in Chennai | Software Testing Training in Chennai | QTP Training in Chennai

  51. Java training in Chennai on Mon, 13th Apr 2015 12:51 pm
  52. Bind blowing. You have achieved your target. People should follow your blog for getting more information and their basic needs.

    Java training in Chennai | Android training in chennai | Java training in Chennai

  53. Android Training on Tue, 21st Apr 2015 8:36 am
  54. I agree with your post. Android software development kit makes the application development process lot simpler and effective. You can create best performing android application with ease. Android Training Institutes in Chennai | Android Training in Chennai

  55. jercy on Tue, 21st Apr 2015 10:08 am
  56. SAS is a type of Business Analytics Software.In these several topics are involved.Retriving the values in the dataset is the important one.These blog explained very clearely about the topic.It really nice.
    Loadrunner Training in Chennai | Loadrunner Training in Chennai | Loadrunner Training in Chennai

  57. Android Training on Tue, 21st Apr 2015 12:21 pm
  58. Hi, I am Emi lives in Chennai. I am technology freak. I did Android mobile application development course in Chennai at reputed training institutes, this is very usful for me to make a bright carrer in IT industry. So If you looking for best Android Training Institutes in Chennai please visit fita academy. Android Training in Chennai

  59. dhanalakshmi on Fri, 24th Apr 2015 10:17 am
  60. Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me.. I get a lot of great information from this blog. Thank you for your sharing this informative blog. Android Training in chennai | Android Training chennai | Android course in chennai | Android course chennai

  61. John on Thu, 7th May 2015 1:03 pm
  62. PROC CONTENTS would be the quickest way to get that information in a dataset. Column names can be found in the column NAME.proc contents data=sashelp.class out=contents noprint; run;

    SAP Success factor Training in Chennai

  63. Testing Training in Chennai on Wed, 10th Jun 2015 7:13 am
  64. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.

    Regards..
    Software Testing Training in Chennai

  65. emtvnews on Mon, 15th Jun 2015 8:09 am
  66. Emtv helps us to provide updated Papua new guinea( PNG ) news, PNG Videos, PNG Article, PNG business, PNG technology, PNG weather, PNG Real Estate, PNG Fashion News, PNG Hollywood movies and other PNG News as well.

    PNG Travel News

  67. sdfasdf on Mon, 15th Jun 2015 8:11 am
  68. TOP Class Backlinks SERVICES PROVIDER

    High PageRank Backlinks

  69. lucy on Thu, 30th Jul 2015 12:43 pm
  70. Benefits of SAS:
    Increases your career opportunities and marketability.
    Enhances your credibility as a technical professional.
    Enhances your knowledge of SAS software.
    Helps you to earn industry validation(certifications) for your knowledge.
    SAS is marked as hot technology in many industries and thus SAS experts are getting higher salaries than others.
    40,000 – 50,000 SAS Analyst and programmer will be required in the next couple of years
    SAS Consultants are highly paid , get quick promotions, their Job is secured WORLDWIDE.
    List of companies currently hiring in SAS — > GE,Genpact, Standard Chartered Bank,Fidelity, HSBC,HP,,Accenture,Novaritis,Target,Mu Sigma, WIPRO,TCS,IBM India,Hewitt,Bank Of America,American, Express,Glaxo,Ranbaxy,Cipla,Inductis, Mercer,Ameriprise,WNS,Barclays,Steria,Tech Mahindra,HCL,Dunnhumby,Infosys,United Health Group,CSC and many more.

  71. juliaroberts on Wed, 5th Aug 2015 2:18 pm
  72. This was so useful and informative. The article helped me to learn something new.

  73. srisathvika on Thu, 6th Aug 2015 1:22 pm
  74. Good to learn something new from this blog. Thanks for sharing such worthy article.

  75. SAS Online Training on Wed, 19th Aug 2015 7:41 am
  76. Great information & very useful blog.

  77. sas training in Chennai on Fri, 21st Aug 2015 12:11 pm
  78. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.

  79. Swetha on Wed, 26th Aug 2015 5:01 am
  80. Are there any statements\functions capable of get the name of variables? Preferrably putting them into a column of another data set, a text field or a macro variable.

  81. SAS advance Training in Chennai on Wed, 26th Aug 2015 5:34 am
  82. First off, I assume you are talking about SAS/Base not SAS/IML; SAS/IML is essentially similar to R and has the same kind of operations available in the same manner.

    SAS/Base is more similar to a database language than a matrix language (though has some elements of both, and some elements of an OOP language, as well as being a full-featured functional programming language).

    As a result, you do things somewhat differently in order to achieve the same goal. Additionally, because of the cost of moving data in a large data table, you are given multiple methods to achieve the same result; you can choose the appropriate method for the required situation.

    To begin with, you generally should not store data in a macro variable in the manner you suggest. It is bad programming practice, and it is inefficient (as you have already noticed). SAS Datasets exist to store data; SAS macro variables exist to help simplify your programming tasks and drive the code.

    Creating the dataset “b” as above is trivial in Base SAS:

    data b;
    set a;
    keep v2;
    run;

  83. SAS training in chennaii on Wed, 26th Aug 2015 5:36 am
  84. That creates a new dataset with the same rows as A, but only the second column. KEEP and DROP allow you to control which columns are in the dataset.

    However, there would be very little point in this dataset, unless you were planning on modifying the data; after all, it contains the same information as A, just less. So for example, if you wanted to merge V2 into another dataset, rather than creating b, you could simply use a dataset option with A:

    data c;
    merge z a(keep=v2);
    by id;
    run;

Tell me what you're thinking...





  • Swiss Association for Analytics

  • Most Popular Posts

  • T-shirts, Mugs & Mousepads


    All benefits given to a charity association
  • Data Mining Search Engine

    Supported by AnalyticBridge

  • Archives

  • Reading Recommandations